 |  |  |  |  |  |  |  |
Problem using JOIN with six tables
Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases
Thread Solved
 |
•
•
Join Date: May 2008
Posts: 3
Reputation: 
Solved Threads: 0
Newbie Poster
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:
What I want to achieve is the following (in one query):
So basically, the output I'm trying to get should look something like this:
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:
The query above gives me the following (unwanted) result:
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.
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:
MS SQL Syntax (Toggle Plain Text)
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)
The query above gives me the following (unwanted) result:
MS SQL Syntax (Toggle Plain Text)
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.
•
•
Join Date: Feb 2005
Posts: 1,181
Reputation:
Solved Threads: 67
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.
•
•
Join Date: May 2008
Posts: 3
Reputation:
Solved Threads: 0
Newbie Poster
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:
And this is what I'm getting (only one contact):
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)...
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)
And this is what I'm getting (only one contact):
MS SQL Syntax (Toggle Plain Text)
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)...
•
•
Join Date: Apr 2008
Posts: 296
Reputation:
Solved Threads: 42
Posting Whiz in Training
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
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
•
•
Join Date: Apr 2008
Posts: 296
Reputation:
Solved Threads: 42
Posting Whiz in Training
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)
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)
Try this code
MS SQL Syntax (Toggle Plain Text)
Hence Wijaya
www.ex-Soft.tk
www.ex-Soft.tk
•
•
Join Date: May 2008
Posts: 3
Reputation:
Solved Threads: 0
Newbie Poster
@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!
@huangzhi... that's what I was looking for! It works! Thank you very much!
|
Similar Threads
- Problem (MS SQL)
- Cartesian Product Problem (Oracle)
- Problem with Rewriting Subqueries as Joins (Database Design)
- MySql multiple table query problem.... (MySQL)
- Problem with Rewriting Subqueries as Joins (MS SQL)
- checkboxes on continuous forms (Visual Basic 4 / 5 / 6)
- Problem with OPENXML in UDF (MS SQL)
- using asp array to store db information (ASP)
Other Threads in the MS SQL Forum
- Previous Thread: problem with sql server agent
- Next Thread: SELECT only the latest date yet within the defined period
| Thread Tools | Search this Thread |
You need to join our community in order to build your list of favorite forums. You can visit the forum index for a listing of all forums.
Related Forum Features
Latest MS SQL Posts
- Today's Posts
- Unanswered Threads