•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 363,493 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,379 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MS SQL advertiser:
Views: 617 | Replies: 6 | Solved
•
•
Join Date: May 2008
Location: Texas
Posts: 3
Reputation: 
Rep Power: 0
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:
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.
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb MS SQL Marketplace
Threaded ModeSimilar 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
