Solved

SQL server joining multiple tables in a query

Posted on 2013-11-12
4
258 Views
Last Modified: 2013-11-12
Hello Experts, I'm working with 3 tables in SQL server which I need to join in a query. table1 is HEADER, table 2 is BOX, table 3 is Detail. The detail level has the most records which is 442, however when I do a join using the field which is available in all 3 tables I get 39000+ records back. It seems that the data is multiplied instead of being displayed on 1 line. I'm using inner join command to create the link. Can you please suggest other methods of joining and displaying the data properly? Attached is excel with the 3 tables extracted, the key field which unifies the 3 tables is “Supplier Document Number”
Thank you.
ImportTablesForLinking.xlsx
0
Comment
Question by:EddieIT
[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
  • 2
4 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39642180
>It seems that the data is multiplied instead of being displayed on 1 line.
The most common reason for this is that the JOIN ... ON clause is missing some of the columns that relate the two tables.

This article may help you..
SQL Server: Table Joins Explained!
0
 
LVL 11

Expert Comment

by:Simone B
ID: 39642200
It is the Box and Detail tables that are causing the problem. You will need to join those two tables using the Box Number.

Select .....
from Header H
inner join Detail D on H.[SupplierDocumentNumber] = D.[SupplierDocumentNumber]
inner join Box B on D.[BoxNumber] = B.[BoxNumber]
0
 

Author Comment

by:EddieIT
ID: 39642233
Noted Buttercup1, can you explain on why?
0
 
LVL 11

Accepted Solution

by:
Simone B earned 500 total points
ID: 39642289
Take the Supplier Document Number of 2013801488 as an example. In the Box table, this ID has 54 records, and in the Detail table it has 235 records. As in Jim's article he is quoting, an inner join on the Supplier Document Number between these tables will join each 2013801488 record in the Box table with each 2013801488 record in the Detail table, multiplying the records.

So, for the 2013801488 record in the Box table, each of the 54 rows has a unique Box Number, which can be joined to the same column in the Detail table. That way, although the records are still multiplied, you are multiplying 235 X 1, instead of 235 X 54. Obviously I haven't gone through the entire table. If you find that a Box Number can apply to several Supplier Document Numbers, then you should use both columns in your join, as below.

Select .....
from Header H
inner join Detail D on H.[SupplierDocumentNumber] = D.[SupplierDocumentNumber]
inner join Box B on D.[BoxNumber] = B.[BoxNumber] and D.[SupplierDocumentNumber] = B.[SupplierDocumentNumber]

I hope that makes sense. The quoted article does a nice job of explaining also.
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql, how to change the data type after data loading? 9 61
Use SSRS to email customers? 4 30
SQL Server 2012 and core licensing 5 31
relocating SQL 2000 18 37
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.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

751 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