Ad:

MS SQL Discussion Thread
Marked Solved
Views: 2163

May 9th, 2008

Problem using JOIN with six tables

Expand Post »

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:

(Toggle Plain Text)

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:

MS SQL Syntax (Toggle Plain Text)
name	Doc_A	Doc_B	Doc_C	Doc_D
------------------------------------------------------------
contact2	1	5	0	4
contact5	4	1	2	2
contact8	2	0	1	0
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:

MS SQL Syntax (Toggle Plain Text)
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
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:

MS SQL Syntax (Toggle Plain Text)
name	Doc_A	Doc_B	Doc_C	Doc_D
-----------------------------------------------------------
contact2	20	20	0	20
contact5	16	16	16	16
contact8	2	0	2	0
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.

Similar Threads

Problem in MS SQL
Cartesian Product Problem in Oracle
Problem with Rewriting Subqueries as Joins in Database Design
MySql multiple table query problem.... in MySQL
Problem with Rewriting Subqueries as Joins in MS SQL

recursiveNugget

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since May 2008

Permalink

May 9th, 2008

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.

Last edited by hollystyles; May 9th, 2008 at 7:54 pm.

hollystyles

Reputation Points: 262

Solved Threads: 68

Veteran Poster

Offline

1,181 posts
since Feb 2005

Permalink

May 10th, 2008

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:

MS SQL Syntax (Toggle Plain Text)
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
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):

MS SQL Syntax (Toggle Plain Text)
name	Doc_A	Doc_B	Doc_C	Doc_D
------------------------------------------------------------
contact5	16	16	16	16
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)...

recursiveNugget

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since May 2008

Permalink

May 11th, 2008

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

tesuji

Reputation Points: 158

Solved Threads: 98

Master Poster

Offline

720 posts
since Apr 2008

Permalink

May 11th, 2008

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)

MS SQL Syntax (Toggle Plain Text)
      #     DocA DocB   DocC    DocD
1  2  C2  A1  B3     (NULL)  D1   
1  2  C2  A1  B4     (NULL)  D1
1  2  C2  A1  B6     (NULL)  D1
1  2  C2  A1  B7     (NULL)  D1
1  2  C2  A1  B8     (NULL)  D1
 
1  2  C2  A1  B3     (NULL)  D3   
1  2  C2  A1  B4     (NULL)  D3
1  2  C2  A1  B6     (NULL)  D3
1  2  C2  A1  B7     (NULL)  D3
1  2  C2  A1  B8     (NULL)  D3
 
1  2  C2  A1  B3     (NULL)  D4   
1  2  C2  A1  B4     (NULL)  D4
1  2  C2  A1  B6     (NULL)  D4
1  2  C2  A1  B7     (NULL)  D4
1  2  C2  A1  B8     (NULL)  D4
 
1  2  C2  A1  B3     (NULL)  D8   
1  2  C2  A1  B4     (NULL)  D8
1  2  C2  A1  B6     (NULL)  D8
1  2  C2  A1  B7     (NULL)  D8
1  2  C2  A1  B8     (NULL)  D8
 
1  5  C5  A3  B2      C5     D5
1  5  C5  A4  B2      C5     D5
1  5  C5  A5  B2      C5     D5
1  5  C5  A8  B2      C5     D5
 
1  5  C5  A3  B2      C6     D5
1  5  C5  A4  B2      C6     D5
1  5  C5  A5  B2      C6     D5
1  5  C5  A8  B2      C6     D5
 
1  5  C5  A3  B2      C5     D5
1  5  C5  A4  B2      C5     D5
1  5  C5  A5  B2      C5     D5
1  5  C5  A8  B2      C5     D5
 
1  5  C5  A3  B2      C6     D6
1  5  C5  A4  B2      C6     D6
1  5  C5  A5  B2      C6     D6
1  5  C5  A8  B2      C6     D6
 
1  8  C8  A6  (NULL)  C2     (NULL)
1  8  C8  A7  (NULL)  C2     (NULL)
1  8  C8  A6  (NULL)  C2     (NULL)
1  8  C8  A7  (NULL)  C2     (NULL)
      #     DocA DocB   DocC    DocD
1  2  C2  A1  B3     (NULL)  D1   
1  2  C2  A1  B4     (NULL)  D1
1  2  C2  A1  B6     (NULL)  D1
1  2  C2  A1  B7     (NULL)  D1
1  2  C2  A1  B8     (NULL)  D1

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

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

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

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

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

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

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

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

tesuji

Reputation Points: 158

Solved Threads: 98

Master Poster

Offline

720 posts
since Apr 2008

Permalink

May 12th, 2008

Re: Problem using JOIN with six tables

Try this code

MS SQL Syntax (Toggle Plain Text)
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
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

huangzhi

Reputation Points: 10

Solved Threads: 13

Light Poster

Offline

48 posts
since Feb 2008

Permalink

May 12th, 2008

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!

recursiveNugget

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since May 2008

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: problem with sql server agent

Next Thread in MS SQL Forum Timeline: SELECT only the latest date yet within the defined period

DaniWeb Message

Cancel Changes

User Name
Password