Join table problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Join table problem

Author

Topic

petek
Posting Yak Master

192 Posts

Posted - 2009-02-04 : 03:58:18

Hi all,

Is there a way to join three tables and bring back results from all three tables relative to the join condition:

i am trying:

select
sup_name as "Supplier Name",
sup_Address1 as "Address 1",
sup_Address2 as "Address 2",
sup_Address3 as "Address 3",
sup_Address4 as "Address 4",
sup_Address5 as "Address 5",
sup_PostCode as "Postcode",
sup_Phone as "Telephone Number",
sc_category as "Category",
ssc_category as "Supplier Sub Category",
sdx_acc_no as "Sage Account Number",
div_code as "Division"
from supplier
left outer join suppliercategory on sup_sc_id = sc_id
left outer join suppliersubcategory on sup_ssc_id = ssc_sc_id
left outer join supplierdivisionxref on sup_id = sdx_sup_id and (sdx_suspended = 0)
left outer join division on sdx_div_id = div_id
order by sup_name

but because its a one to many relationship it returns the incorrect results.

Help please.....

Kind Regards

Pete.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-04 : 04:09:34

with out data how can
show some sample data & sample output

petek
Posting Yak Master

192 Posts

Posted - 2009-02-04 : 04:27:00

sorry i dont understand?

Kind Regards

Pete.

petek
Posting Yak Master

192 Posts

Posted - 2009-02-04 : 05:13:36

this worked

SELECT t1.sup_Name AS [Supplier Name], t1.sup_Address1 AS [Address 1], t1.sup_Address2 AS [Address 2], t1.sup_Address3 AS [Address 3],
t1.sup_Address4 AS [Address 4], t1.sup_Address5 AS [Address 5], t1.sup_PostCode AS Postcode, t1.sup_Phone AS [Telephone Number],
t2.sc_category AS Category, t3.ssc_category AS [Supplier Sub Category], t4.sdx_acc_no AS [Sage Account Number], t5.div_Code AS Division
FROM dbo.Supplier t1 INNER JOIN
dbo.SupplierCategory t2 ON t1.sup_sc_id = t2.sc_id INNER JOIN
dbo.SupplierDivisionXref t4 ON t4.sdx_sup_id = t1.sup_ID INNER JOIN
dbo.Division t5 ON t5.div_ID = t4.sdx_div_id INNER JOIN
dbo.SupplierSubCategory t3 ON t2.sc_id = t3.ssc_id
WHERE (t4.sdx_suspended = 0)

Kind Regards

Pete.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources