[PHP-DB] Re: Can you SUM(IF) from two different tables at the same time?
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
[PHP-DB] Re: Can you SUM(IF) from two different tables at the same time?
Richard Davey [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... In the 5th line I changed thread.id to thread.threadid so the name was correct but the same error occurs. Ok, I was guessing at your field names... to give a perfectly satisfactory result but have no idea if this is perversing SQL again or not! (grin) When retrieving information from a database, you should expect to get a variable number of records where each record has the same structure. Instead, you were hard-coding a query to get a single record with information about a number of different groups. If you wanted to change the number of groups returned, you would have to change the structure of the query. By getting a record for each group, the structure of the query does not change if you decide to look at a different number of groups - just the data you give to the WHERE clause. (it at least makes more sense now I guess). As soon as I add the where IN clause it errors. umm... try putting the WHERE clause before the GROUP BY? 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. try a Google search for 'database normalization tutorial'; that should get you started. also check out the documentation at www.mysql.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
