[thelist] MySQL COUNTing

[rudy]

> What I want is to have one SQl query which returns 
> the title and date of the thread, as well as the total number 
> of posts, by joining the THREADS and MESSAGES tables 
> on the THREAD_ID column.
>
>my query currently looks like this, and fails miserably:
>
>$sql = "SELECT
>         threads.title AS 'title',
>         threads.date AS 'date',
>         COUNT(messages.thread_id) AS 'noposts'
>             FROM threads, messages
>             WHERE messages.thread_id = threads.id
>             ORDER BY date DESC
>             LIMIT (20)";


hi kerin

you are so close   ;o)

   SELECT
           threads.title AS 'title',
           threads.date AS 'date',
           COUNT(messages.thread_id) AS 'noposts'
               FROM threads, messages
               WHERE messages.thread_id = threads.id
     GROUP BY threads.title, threads.date
             ORDER BY date DESC
             LIMIT (20)

when you have a one-to-many join, and all you want is
the count of the "many" records, you have to do a GROUP BY
on all the "one" columns in the SELECT list 

let me know if you also need to count 0 for
threads that have no messages


rudy
http://rudy.ca/