Problem using JOIN with six tables
 

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.

Re: Problem using JOIN with six tables
 

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.

Re: Problem using JOIN with six tables
 

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

Re: Problem using JOIN with six tables
 

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

Re: Problem using JOIN with six tables
 

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)

Re: Problem using JOIN with six tables
 

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

Re: Problem using JOIN with six tables
 

@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: