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

"Richard Davey" <rich@fatal-design.com> wrote in message news:20020527233258.75432.qmail@pb1.pair.com... > 1. SELECT SUM(IF(boardid='2',1,0)) AS b2, SUM(IF(boardid='1',1,0)) AS b1, > COUNT(*) AS total FROM thread > > 2. SELECT SUM(IF(boardid='2',1,0)) AS b2, SUM(IF(boardid='1',1,0)) AS b1, > COUNT(*) AS total FROM message > > This literally brings back a total number of threads and messages posted to > the two respective boards (boardid1 and 2). > All I am wondering is if this can be combined into a single query or does > the very nature of using the SUM(IF) not allow this to happen? Your current queries are returning "thread count on all groups" as a single record and "message count on all groups" as a single record. This is a horrible perversion of SQL. 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 )