Solved

SQL - Multiple Joins resulting in too many results.

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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Sum Over Multiple Tables 20 33
disk usage reporting tools 27 52
Get data from two MySQL tables 6 22
SQL 2012 Instance Problem 3 55
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

756 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