Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

SQL - Multiple Joins resulting in too many results.

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
SM17CH
Asked:
SM17CH
2 Solutions
 
Anthony PerkinsCommented:
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
 
Alex MatzingerDatabase AdministratorCommented:
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
 
SM17CHAuthor Commented:
@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
 
Alex MatzingerDatabase AdministratorCommented:
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
 
JoeNuvoCommented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now