Thanx for your reply hollystyles,
I've made a typo... the second column name on tables
tDocumentsA ... tDocumentsD should read contact_id and not user_id, so tDocumentsA.user_id = tUsers.contact_id should be correct. Sorry about that.
I've used INNER JOIN as you said, but now I'm only getting one row.
This is my query:
SELECT tContacts.name, COUNT(tDocumentsA.id) AS 'Doc_A', COUNT(tDocumentsB.id) AS 'Doc_B', COUNT(tDocumentsC.id) AS 'Doc_C', COUNT(tDocumentsD.id) AS 'Doc_D'
FROM tUsers LEFT OUTER JOIN
tContacts ON tContacts.id = tUsers.contact_id INNER JOIN
tDocumentsA ON tDocumentsA.user_id = tUsers.contact_id INNER JOIN
tDocumentsB ON tDocumentsB.user_id = tUsers.contact_id INNER JOIN
tDocumentsC ON tDocumentsC.user_id = tUsers.contact_id INNER JOIN
tDocumentsD ON tDocumentsD.user_id = tUsers.contact_id
WHERE (tUsers.location = '1')
GROUP BY tContacts.name
And this is what I'm getting (only one contact):
name Doc_A Doc_B Doc_C Doc_D
------------------------------------------------------------
contact5 16 16 16 16
When using LEFT OUTER JOIN I get the right people, but incorrect numbers (In the example above, using INNER JOIN, I'm also getting the incorrect numbers).
contact5 should have (Doc_A ... Doc_D): 4, 1, 2, 2. Instead, I'm getting: 16, 16, 16, 16. It seems to be multiplying the column values (4 x 1 x 2 x 2 = 16)...
