SQLTeam.com | Weblogs | Forums

Query help please

sql2012

#1

Hi I am new to SQL , I AM FINDING DIFFICULT JOIN 4 TABLES TO GETHER . I AM JOINING FIRST 2 TABLES WITH 4 CLOUMNS , BUT I AM UNABLE TO JOIN 3RD AND 4TH TABLES . PLEASE YOUR HELP WILL BE REALLY APPRIACIATED.

HERE IS 1ST 2 TABLES JOIN

    [Item No_]
   ,[Gross Weight]
   ,[Reason Code]='RTN_DAMAGE'
   ,[Internet Order No_]   

FROM [TBW_BI].[dbo].[Dreams house$Warehouse Returns] AS RE
JOIN [TBW_BI].[dbo].[Dreams house$Sales Invoice Line] AS LI
ON RE.[Item No_]=LI.[No_]

here are the colums and tables which I want to join :
[Ship-to Country Code] ,[Internet Order Reference] FROM [TBW_BI].[dbo].[Dreams house$Internet Order Header]
and
[Shipping Agent Code], [Internet Order Reference] FROM [TBW_BI].[dbo].[Dreams house$Internet Consignment]

final result must contain ,
order number , ship to city , gross weight , reason code and item.no

Please help.


#2

What you will need to do is to figure out which column (or columns) in two tables you want to join together. Without knowing the columns in your tables, it is hard to specifically say what you should do. But your thought process should be something like this:
In the [TBW_BI].[dbo].[Dreams house$Internet Order Header] what is the column that matches a column in the two tables you have already joined together? Perhaps there is a "Header_No" column in the [TBW_BI].[dbo].[Dreams house$Internet Order Header] table that corresponds to the [No_] column in [TBW_BI].[dbo].[Dreams house$Sales Invoice Line] table? (I just made that up. Most likely there is no column named "Header_No" in your table) If there is, then your join would be like this:

select 
[Item No_]
   ,[Gross Weight]
   ,[Reason Code]='RTN_DAMAGE'
   ,[Internet Order No_]
FROM [TBW_BI].[dbo].[Dreams house$Warehouse Returns] AS RE 
JOIN [TBW_BI].[dbo].[Dreams house$Sales Invoice Line] AS LI
ON RE.[Item No_]=LI.[No_]
JOIN [TBW_BI].[dbo].[Dreams house$Internet Order Header] as IOH
ON IOH.Header_No = LI.[No_]

If that does not give you enough clues about how to proceed, post the DDL for the tables. DDL is Data Definition Language. See here, in particular Kristen's reply with a few links for help on how to post DDLs.