Problem using JOIN with six tables

[recursiveNugget]

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:

[B]Table: tUsers[/B]

id	location	contact_id
-----------------------------------------
1	1	2
2	2	4
3	1	5
4	1	8


[B]Table: tContacts[/B]

id	name
-------------------------------
1	contact1
2	contact2
3	contact3
4	contact4
5	contact5
6	contact6
7	contact7
8	contact8
9	contact9
10	contact10


[B]Table: tDocumentsA[/B]

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


[B]Table: tDocumentsB[/B]

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


[B]Table: tDocumentsC[/B]

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


[B]Table: tDocumentsD[/B]

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.

[hollystyles]

It's late and I'm a little blurry. But from what I can see in your query I think this: tDocumentsA.user_id = tUsers.contact_id should be: tDocumentsA.user_id = tUsers.id (the same for all document table joins) And all your JOINS should be INNER JOIN not LEFT OUTER JOIN.

[recursiveNugget]

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)... :(

[tesuji]

Hi,

Left outer join should be replaced by inner join, as already stated. Can you post the create- table statements? I have got the feeling that something could be wrong with the relationships of your tables. As for example, table tUser contains foreign key contact_id what comes from tContacts. If tUser.id is the primary key then one certain user can only have ONE contact. Maybe that s correct? If a user should have more then one contact, the tUser.id must be foreign key of tContacts. Furthermore, primary key of tContacts, obviously tContacts.id, should then be non-identifying foreign key of the tdocumentsA...D instead of user_id.

As for your four document-tables, it would be a good idea to think of better normalization: What if you get further categories, say tdocumentsE ... ? So all documents should be stored in ONE table. This approach leads to simpler SQL statements, too.

krs,
tesu

[tesuji]

hi recursiveNugget,

bad news! I have checked your join operations on those 6 tables, and the (wrong) results are:

1. contact.id
id 2 will be counted 20 times
id 5 will be counted 16 times
id 8 will be counted 4 times

2. Indeed, you need LEFT OUTER JOIN. With inner join (which would be the appropriate one
for such tasks, but...) you would lose all information about contact.id 2 and 8.

3. However, the result is wrong because you join four tables (tdocumentsA..D) independently
together. The information you have spread over these 4 table MUST be arranged in ONE
table only. Then applying of INNER JOIN on tusers INNER JOIN tContacts INNER JOIN
tdocuments would work properly.

Long story short, you need to redesign your tables.

krs,
tesu

Btw, below is the result of the last join, where you can check out why inner join would produce poorer results (all rows containing (NULL) would then disappear)

#     DocA DocB   DocC    DocD
1  1  2  C2  A1  B3     (NULL)  D1   
1  1  2  C2  A1  B4     (NULL)  D1
1  1  2  C2  A1  B6     (NULL)  D1
1  1  2  C2  A1  B7     (NULL)  D1
1  1  2  C2  A1  B8     (NULL)  D1

1  1  2  C2  A1  B3     (NULL)  D3   
1  1  2  C2  A1  B4     (NULL)  D3
1  1  2  C2  A1  B6     (NULL)  D3
1  1  2  C2  A1  B7     (NULL)  D3
1  1  2  C2  A1  B8     (NULL)  D3

1  1  2  C2  A1  B3     (NULL)  D4   
1  1  2  C2  A1  B4     (NULL)  D4
1  1  2  C2  A1  B6     (NULL)  D4
1  1  2  C2  A1  B7     (NULL)  D4
1  1  2  C2  A1  B8     (NULL)  D4

1  1  2  C2  A1  B3     (NULL)  D8   
1  1  2  C2  A1  B4     (NULL)  D8
1  1  2  C2  A1  B6     (NULL)  D8
1  1  2  C2  A1  B7     (NULL)  D8
1  1  2  C2  A1  B8     (NULL)  D8

3  1  5  C5  A3  B2      C5     D5
3  1  5  C5  A4  B2      C5     D5
3  1  5  C5  A5  B2      C5     D5
3  1  5  C5  A8  B2      C5     D5

3  1  5  C5  A3  B2      C6     D5
3  1  5  C5  A4  B2      C6     D5
3  1  5  C5  A5  B2      C6     D5
3  1  5  C5  A8  B2      C6     D5

3  1  5  C5  A3  B2      C5     D5
3  1  5  C5  A4  B2      C5     D5
3  1  5  C5  A5  B2      C5     D5
3  1  5  C5  A8  B2      C5     D5

3  1  5  C5  A3  B2      C6     D6
3  1  5  C5  A4  B2      C6     D6
3  1  5  C5  A5  B2      C6     D6
3  1  5  C5  A8  B2      C6     D6

4  1  8  C8  A6  (NULL)  C2     (NULL)
4  1  8  C8  A7  (NULL)  C2     (NULL)
4  1  8  C8  A6  (NULL)  C2     (NULL)
4  1  8  C8  A7  (NULL)  C2     (NULL)

[huangzhi]

Try this code

select	c.name, isNull(doc_A.Doc_A, 0) as Doc_A, isNull(doc_B.Doc_B, 0) as Doc_B, isNull(doc_C.Doc_C, 0) as Doc_C, isNull(doc_D.Doc_D, 0) as Doc_D
  from	tUsers u inner join tContacts c on
	u.contact_id = c.id left join (select user_id, count(*) as Doc_A from tDocumentsA group by user_id) Doc_A on
	c.id = Doc_A.user_id left join (select user_id, count(*) as Doc_B from tDocumentsB group by user_id) Doc_B on
	c.id = Doc_B.user_id left join (select user_id, count(*) as Doc_C from tDocumentsC group by user_id) Doc_C on
	c.id = Doc_C.user_id left join (select user_id, count(*) as Doc_D from tDocumentsD group by user_id) Doc_D on
	c.id = Doc_D.user_id
  where u.location = 1

[recursiveNugget]

@tesuji... the documents must be in separate tables since they will have different fields... the ones I've shown are just to illustrate the problem. Thank you for your help. :)

@huangzhi... that's what I was looking for! It works! Thank you very much! :cool: