Solved

SQL - Multiple Joins resulting in too many results.

Posted on 2011-03-15
5
346 Views
Last Modified: 2012-08-13
Hi,

Ive tried to write a query with multiple inner joins. I have 3 tables (TBL_1, TBL_2, TBL_3) all linked by the field ACCOUNT_ID.

I need to join the tables so I can acess fields from all three tables that are linked to the ACCOUNT ID.

I can get one inner join to work, but when I tack on a second my results a multiplied 3000 times when there should only be 1 or 2 results.

If anyone can provide a simple example I would appreciate it. All the ones Ive found on google are overly complicated and I dont understand how to implement them.

Cheers
0
Comment
Question by:SM17CH
5 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35143391
The problem is that your 3rd table has multiple rows with the same account ID hence the duplicates.  You need to define what conditions you want to set for that third table so that it only includes the one row.

Word of caution:  Do not get suckered into using "DISTINCT to solve all you problems".  Analyze what you want to return first.
0
 
LVL 4

Accepted Solution

by:
Alex Matzinger earned 350 total points
ID: 35143396
Something Like this
SELECT * 
FROM TBL_1
INNER JOIN TBL_2
ON TBL_1.ACCOUNT_ID =TBL_2.ACCOUNT_ID
INNER JOIN TBL_3
ON TBL_3.ACCOUNT_ID=TBL_1.ACCOUNT_ID AND TBL_3.ACCOUNT_ID = TBL_2.ACCOUNT_ID
WHERE <CONDITIONS>

Open in new window

0
 
LVL 2

Author Comment

by:SM17CH
ID: 35143412
@acperkins - Im not sure if that is right because if I query the Account ID on the third table by itself only 3 records exist. I dont know where the 3000 duplicates are coming from.

SELECT *
FROM TBL_1
INNER JOIN TBL_2
ON TBL_1.ACCOUNT_ID=TBL_2.ACCOUNT_ID
INNER JOIN TBL_3
ON TBL_1.ACCOUNT_ID=TBL_2.ACCOUNT_ID
WHERE TBL_2.PERSON_ID = 2331
AND ACCOUNT_TYPE_ID = 15

Open in new window

0
 
LVL 4

Expert Comment

by:Alex Matzinger
ID: 35143421
SELECT *
FROM TBL_1
INNER JOIN TBL_2
ON TBL_1.ACCOUNT_ID=TBL_2.ACCOUNT_ID
INNER JOIN TBL_3
ON TBL_1.ACCOUNT_ID=TBL_2.ACCOUNT_ID <--- This needs to be TBL_3.ACCOUNT_ID=TBL_2.ACCOUNT_ID
WHERE TBL_2.PERSON_ID = 2331
AND ACCOUNT_TYPE_ID = 15

Open in new window

0
 
LVL 11

Assisted Solution

by:JoeNuvo
JoeNuvo earned 150 total points
ID: 35143742
as amatzinger point out, the error is happen due to you forget to put join condition over TBL_3.
so, you get result of join from TBL1/TBL_2 multiply by TBL_3

you need either

INNER JOIN TBL_3
ON TBL_1.ACCOUNT_ID=TBL_3.ACCOUNT_ID

or

INNER JOIN TBL_3
ON TBL_2.ACCOUNT_ID=TBL_3.ACCOUNT_ID
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Count with a subquery showing details 10 42
SQL Server Designer 19 39
export sql results to csv 6 34
ServiceCenter IR Query Expressions 1 14
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question