Solved

SQL - Multiple Joins resulting in too many results.

Posted on 2011-03-15
5
350 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

636 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