How can I set a query for having two Join Relations in one table?

byMR

September 2, 2022

I’m new in sql and I’m trying to write a query that merge the results of two queries that work.

I have two tables :

tblPart[ partnr, manufacturer, supplier]

data ex:

 A123 , MAN1 , SUP1
 A125 , MAN1 , SUP2

tblAddress [shortname, longname]

data ex:

   MAN1, "Siemens s.p.a."
   SUP1, "Sotre 1"
   SUP2, "Store 2"

In the columns of tblPart I have the shortname value for both the fields (manufacturer, supplier) and in the tblAddress I have the longname.
In this moment I have two query:

 SELECT tblPart.partnr,tblPart.manufacturer, tblAddress.longname, tblPart.supplier
        FROM tblAddress RIGHT OUTER JOIN 
        tblPart ON tblAddress.shortname = tblPart.manufacturer

and the second one:

 SELECT  tblPart.partnr,tblPart.manufacturer,  tblPart.supplier, tblAddress.longname
         FROM tblAddress RIGHT OUTER JOIN
         tblPart ON tblAddress.shortname = tblPart.supplier

With the UNION operator I have double values. Where I’m in wrong?It’s possible to have only one table with : partnr, manufacturer, longname(manufacturer),supplier, longname(supplier)

data ex:

 A123 , MAN1 ,"Siemens s.p.a.",  SUP1, "Sotre 1"
 A125 , MAN1 ,"Siemens s.p.a.",  SUP2, "Sotre 2"

>Solution :

I suspect what you need to do if JOIN twice to your table tblAddress. To do this you’ll need to reverse the joins.

SELECT P.partnr,
       P.manufacturer,
       M.longname AS ManufacturerLongname
       P.supplier,
       S.longname AS SupplierLongname
FROM dbo.tblPart P
     LEFT JOIN dbo.tblAddress M ON P.manufacturer = M.shortname
     LEFT JOIN dbo.tblAddress S ON P.supplier = M.shortname;