Sql Join tables and get data from both on same column

Question

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

Could you add some data with expected results? seems the explanation is not clear as you are mentioning about number of rows returned. — indika ranaweera
– indika ranaweera, Commented Jul 16, 2019 at 14:36
Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3) — marc_s
– marc_s, Commented Jul 16, 2019 at 15:22
This is one of the most confusing questions I have ever read, can you edit the question with DDL to create the sample tables [SALES] and [REFERENCES] (which is a keyword and should be discouraged), and also include the output you want? — MJH
– MJH, Commented Jul 16, 2019 at 15:33
i know it's confusing but i alreayd managed to get the result intended, see the final edit on my post. I never used UNION before so i thought i had to do it with join — Jackal
– Jackal, Commented Jul 16, 2019 at 15:49

indika ranaweera · Accepted Answer · 2019-07-16 18:29:54Z

2

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

edited Jul 16, 2019 at 18:29

answered Jul 16, 2019 at 15:20

indika ranaweera

1165 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Jackal Over a year ago

this won't work because the references that don't exist on table B won't appear

indika ranaweera Over a year ago

I am not sure whether you looked at the output of this with the output that you have mentioned. it matches with what you want, if you are expecting different output then i would recommend updating the output

indika ranaweera Over a year ago

Added the output for your reference

Jackal Over a year ago

See my last edit to my post, i already managed to get the result intended. This was not possible with join only because i had to use union to get the results that don't exist on right table

indika ranaweera Over a year ago

I am not sure about your answer "This was not possible with join" which i have already matched the output. so i will leave it up to you to use which one, but joins can be used and i do not see a difference in the output, i updated the output.

|

HereGoes · Accepted Answer · 2019-07-16 15:08:41Z

0

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

edited Jul 16, 2019 at 15:08

answered Jul 16, 2019 at 14:39

HereGoes

1,3201 gold badge9 silver badges15 bronze badges

1 Comment

Jackal Over a year ago

if i do left join the References that do not appear on the right table, won't appear. There are references on the left table that aren't on the right one and i need those to appear on the final result too

Collectives™ on Stack Overflow

Sql Join tables and get data from both on same column

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related