Hello all,
I'm having some problems with joins in SQL. I hope someone here can help me out a little bit. I'm using MS SQL 2000 Server.
I have the following tables & data:
Table: tUsers
id location contact_id
-----------------------------------------
1 1 2
2 2 4
3 1 5
4 1 8
Table: tContacts
id name
-------------------------------
1 contact1
2 contact2
3 contact3
4 contact4
5 contact5
6 contact6
7 contact7
8 contact8
9 contact9
10 contact10
Table: tDocumentsA
id user_id doc
-------------------------------------------------------
1 2 documentA1
2 1 documentA2
3 5 documentA3
4 5 documentA4
5 5 documentA5
6 8 documentA6
7 8 documentA7
8 5 documentA8
Table: tDocumentsB
id user_id doc
-----------------------------------------------------
1 4 documentB1
2 5 documentB2
3 2 documentB3
4 2 documentB4
5 1 documentB5
6 2 documentB6
7 2 documentB7
8 2 documentB8
Table: tDocumentsC
id user_id doc
------------------------------------------------------
1 1 documentC1
2 8 documentC2
3 4 documentC3
4 3 documentC4
5 5 documentC5
6 5 documentC6
7 4 documentC7
8 1 documentC8
Table: tDocumentsD
id user_id doc
------------------------------------------------------
1 2 documentD1
2 3 documentD2
3 2 documentD3
4 2 documentD4
5 5 documentD5
6 5 documentD6
7 3 documentD7
8 2 documentD8
What I want to achieve is the following (in one query):
- Get the id's of the contacts that are users (not all contacts are users) and belong to location 1
- Display a list of all the contact-users, along with the number of documents "A", documents "B", documents "C", and documents "D" that each contact has created.
So basically, the output I'm trying to get
should look something like this:
name Doc_A Doc_B Doc_C Doc_D
------------------------------------------------------------
contact2 1 5 0 4
contact5 4 1 2 2
contact8 2 0 1 0
I'm using the following query, but for some reason, it seems to be multiplying some values, so I'm not getting the right numbers:
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 LEFT OUTER JOIN
tDocumentsA ON tDocumentsA.user_id = tUsers.contact_id LEFT OUTER JOIN
tDocumentsB ON tDocumentsB.user_id = tUsers.contact_id LEFT OUTER JOIN
tDocumentsC ON tDocumentsC.user_id = tUsers.contact_id LEFT OUTER JOIN
tDocumentsD ON tDocumentsD.user_id = tUsers.contact_id
WHERE (tUsers.location = '1')
GROUP BY tContacts.name
The query above gives me the following (unwanted) result:
name Doc_A Doc_B Doc_C Doc_D
-----------------------------------------------------------
contact2 20 20 0 20
contact5 16 16 16 16
contact8 2 0 2 0
I'm not very used to working with Joins, so I haven't been able to figure out what's wrong with my query.
I will greatly appreciate your help. Thank you.