Re: Can you SUM(IF) from two different tables at the same time?

"Hugh Bothwell" <hugh_bothwell@hotmail.com> wrote in message news:20020528143047.77860.qmail@pb1.pair.com... > 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