COUNT() from multiple tables?

Is there a way to get the separate number of records of three tables in one query? I was using this one:



And it works... except for when the record count of any one of the tables is 0, in which case it will return 0 as the record count for all tables. Using a UNION causes similar problems.

Any ideas?

Hey j_freeman!
That query returns 0 simply because the FROM t1, t2, t3 makes a combination of all records. Therefore, if a table has no records, the complete combination is null and everything returns 0.

Is there a relationship between the tables or are they simply three tables? (I ask about FKs and that).

There are many ways to aproach this (if the tables are not related):

SELECT
(SELECT COUNT(DISTINCT id) FROM member) AS members,
(SELECT COUNT(DISTINCT id) FROM thread) AS threads,
(SELECT COUNT(DISTINCT id) FROM post) AS posts

another way is to write down a Stored Procedure and asign every count to a variable. Then, return:

SELECT @count1, @count2, @count3

If the tables are related a succesive call to a left join would do the trick!

Good Luck!

ANibal.

Hello Anibal.

Yes the tables are indeed related. Here's the relevant structure of the three tables:

MEMBER
id - int(10)

THREAD
id - int(10)
author - int(10) <-- This corresponds to the ID field of the MEMBER table.

POST
id - int(10)
parent - int(10) <-- This corresponds to the ID field of the THREAD table.
author - int(10) <-- This corresponds to the ID field of the MEMBER table.

I'm now using this query:


And I think it works for what I need.