[thelist] Grouping COUNT fields from multiple tables in mySQL

[Richard Harb]

... searched the docs, the web but didn't find anything conclusive.

I'm running mySQL 4.0x

I have (at least) 3 tables:
user: uid, name
item: item_id, owner_id, name
stuff: stuff_id, owner_id, name

I'd like to generate a report that shows how many records in the item and stuff
table are owned by a user - preferably with one query.

the query:

SELECT user.name, COUNT(item.item_id) item_count
FROM user LEFT JOIN item ON user.uid = item.owner_id
GROUP BY user.name

works fine as long as only one COUNT / LEFT JOIN is present, however I can not
come up with something that includes the COUNT from the `user_stuff_nn` table as
well - and have a correct count. Like:

SELECT user.name, COUNT(item.item_id) item_count, COUNT(stuff.stuff_id) stuff_count
...

Is there a way to have the totals of more that one joined table (grouped by the
same field)?

I can run multiple queries and join the result in the app logic later, but I
thought there ought to be a more elegant way of doing it.

I'm a little embarrassed because I think it must be fairly basic, but still ...

Thanks!

Richard