SQLTeam.com | Weblogs | Forums

Joining multiple tables

Hi, I've got an interesting query and I'm wondering if anyone can assist.

I have a table with Order Id's, and 4 other tables with various details about the orders.

Table 1
Order ID
1234

Table 2
Order ID Priceperunit
1234 0.23

Table 3
Order ID Retail Price Locator Code
1234 0.78 34567

Table 4
Order ID Distributor Code Supplier Code
1234 7654 3567

Id love to be able to create a statement using Table 1 at its base, that brings through Priceperunit, Retail Price, Locator Code, Distributor Code and Supplier Code, along with all the fields that are already in Table 1.

Eg) (I've missed out the remaining field here as I've run out of space!)
Order ID Priceperunit Retail Price Locator Code
1234 0.78 0.23 34567

Order ID is the key field on all of the tables.

Ive been trying with Joins and the UNION and UNION ALL commands and have got stuck.

Can anyone help me please?

Thank you.

hope this helps

select 
	Table1.OrderID 			, 
	Table2.Priceperunit 	, 
	Table3.RetailPrice		,Table3.LocatorCode ,
	Table4.DistributorCode	,Table4.SupplierCode
from 
	Table1 
		join Table2 on Table1.OrderID = Table2.OrderID
		join Table3 on Table1.OrderID = Table3.OrderID
		join Table4 on Table1.OrderID = Table4.OrderID