"Hugh Bothwell" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > Instead, you want a query which returns a record > with "thread count" and "message count" for each > group. > > SELECT > COUNT(DISTINCT threadid) AS threads, > COUNT(*) AS messages > FROM > thread LEFT JOIN message ON message.threadid=thread.id > GROUP BY boardid ASC > WHERE boardid IN ( 1, 2 )
Thanks for the query Hugh, but it gives me: "You have an error in your SQL syntax near 'WHERE boardid IN(1,2)' at line 7" In the 5th line I changed "thread.id" to "thread.threadid" so the name was correct but the same error occurs. After much testing I managed to get the following: SELECT COUNT(DISTINCT thread.threadid) AS threads, COUNT(*) AS messages, thread.boardid FROM thread LEFT JOIN message ON message.threadid=thread.threadid GROUP BY thread.boardid ASC to give a perfectly satisfactory result but have no idea if this is perversing SQL again or not! (it at least makes more sense now I guess). As soon as I add the "where IN" clause it errors. Can anyone recommend ANY good books on SQL query design? I don't want (or care) about database administration, I just want to know what constitutes a good database design and lots and lots of query examples/tutorials. Cheers, Richard -- Fatal Design http://www.fatal-design.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
