EddieIT
asked on
SQL server joining multiple tables in a query
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
Thank you.
ImportTablesForLinking.xlsx
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]
Select .....
from Header H
inner join Detail D on H.[SupplierDocumentNumber]
inner join Box B on D.[BoxNumber] = B.[BoxNumber]
ASKER
Noted Buttercup1, can you explain on why?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!