CODEWITHSUNDEEP
sql-server
Jackal
Jackal

Reputation: 3521

Sql Join tables and get data from both on same column

EDIT Here is my problem simplified

I need data from Sales and References table

Sales returns me data with amount transfered

References returns me data with the amount to supply and is filetered by inventory

Basically i need to get the results that match References but i need to also add the results that do not appear on the References table but these are ignored by the where clause

Here is the actual updated query i used in the beginning

SELECT 
    A.Reference,
    B.QtyToSupply,
    A.QtyTransfered
FROM 
    SALES A
RIGHT JOIN 
    REFERENCES B ON A.Reference = B.Reference
WHERE B.InventoryId = 1

UPDATE EXAMPLE

SELECT 
    Reference,
    QtyTransfered
FROM 
    SALES

This returns this data:

  Reference   |  QtyTransfered
     M1              200
     M1              200
     M2              200
     M4              500
     M5              250
     M6              300

The joined query returns this data

SELECT 
    A.Reference,
    B.QtyToSupply,
    A.QtyTransfered
FROM 
    SALES A
RIGHT JOIN 
    REFERENCES B ON A.Reference = B.Reference
WHERE B.InventoryId = 1


  Reference   |  QtyToSupply    | QtyTransfered
     M2              200              200
     M4              500              500
     M6              300              300
     M9              800              NULL
     M10             800              NULL

With left join

Reference   |  QtyToSupply    | QtyTransfered
     M2              200              200
     M4              500              500
     M6              300              300

The output should be 

   Reference   |  QtyToSupply    | QtyTransfered
         M2              200              200
         M4              500              500
         M6              300              300
         M9              800              NULL
         M10             800              NULL
         M1              NULL             200
         M1              NULL             200
         M5              NULL             250

FINAL RESULT

Here is how i managed to get the result intended despite the confusion since i haven't ever used union before.

   SELECT 
        A.Reference,
        B.QtyToSupply,
        A.QtyTransfered
    FROM 
        SALES A
    RIGHT JOIN 
        REFERENCES B ON A.Reference = B.Reference
    WHERE B.InventoryId = 1
    UNION
    SELECT 
        S.Reference,
        R.QtyToSupply,
        S.QtyTransfered
    FROM 
        SALES S
    WHERE NOT EXISTS ( SELECT 1 FROM References R WHERE R.Reference= S.Reference)

.I'll edit the question later to look less confusing for people who go through the same problem

Upvotes: 0

Views: 740

Answers (2)

indika ranaweera
indika ranaweera

Reputation: 116

You need a full join see following SQL 

    create table #SALES 
    (
    Reference varchar(5),
    QtyTransfered int
    )

    create table #REFERENCES 
    (
    Reference varchar(5),
    QtyToSupply int
    )

    insert into #SALES
    select 'M1',200 UNION ALL
    select 'M1',200 UNION ALL
    select 'M2',200 UNION ALL
    select 'M4',500 UNION ALL
    select 'M5',250 UNION ALL
    select 'M6',300 

    insert into #REFERENCES 
    select 'M2' ,200 UNION ALL   
    select 'M4' ,500 UNION ALL    
    select 'M6' ,300 UNION ALL    
    select 'M9' ,800 UNION ALL    
    select 'M10',800       

    SELECT 
    COALESCE(b.Reference,a.Reference)Reference,
    B.QtyToSupply,
    A.QtyTransfered
    FROM #SALES A
    full JOIN #REFERENCES B
    ON A.Reference = B.Reference
    order by 2 DESC

    DROP TABLE #REFERENCES
    DROP TABLE #SALES

adding the output 

     Reference QtyToSupply QtyTransfered
    --------- ----------- -------------
    M9        800         NULL
    M10       800         NULL
    M4        500         500
    M6        300         300
    M2        200         200
    M5        NULL        250
    M1        NULL        200
    M1        NULL        200

Upvotes: 2

HereGoes
HereGoes

Reputation: 1320

What if you did subqueries along with the UNION ALL....

SELECT  A.Reference,
   (SELECT b. qtytosupply FROM "REFERENCES" b where A.Reference = B.Reference) QtyToSupply,
   A.QtyTransfered
FROM SALES A 
union all
SELECT 
   A.Reference,
   A.QtyToSupply,
    (SELECT b.qtytransfered FROM SALES b where A.Reference = B.Reference)  QtyTransfered
FROM "REFERENCES" A

Upvotes: 0

Related Questions