Solved

SQL - Multiple Joins resulting in too many results.

Posted on 2011-03-15
5
347 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

856 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