CODEWITHSUNDEEP
sqloracle-database
user6111211
user6111211

Reputation:

Join multiple columns, filling non-existent values with 0

I have 2 tables, staff_product and orders_with_over_20k_product.

staff_product

  STAFF_ID PRODUCT_ID
---------- ----------
         2          6
         2          4
         2          5
         2          7
         1          6
         1          4
         1          5
         1          7
         3          6
         3          4
         3          5
         3          7

orders_with_over_20k_product

  STAFF_ID PRODUCT_ID         PQ
---------- ---------- ----------
         1          4         20
         2          5        100
         1          5         10
         1          7          8
         2          7          1
         2          4         10
         2          6        100
         1          6        100
         3          4          1

I want to join them such that if there is a record in staff_product that has matching values in orders_with_over_20k_product then PQ will be copied, and if there isn't then PQ will be set to 0. I also want to preserve the order or product_id in staff_product (it's important for how my program handles this query). The desired output is as follows 

  STAFF_ID PRODUCT_ID        PQ
---------- ---------- ---------
         2          6       100
         2          4        10
         2          5       100
         2          7         1
         1          6       100
         1          4        20
         1          5        10
         1          7         8
         3          6         0
         3          4         1
         3          5         0
         3          7         0

I'll be executing this select in a stored procedure and both the existing tables are currently views made of different queries. How would construct a query for my desired output?

Upvotes: 0

Views: 34

Answers (3)

Ajeet Verma
Ajeet Verma

Reputation: 1123

Please use below SQL code for this issue.

SELECT A.STAFF_ID
    ,A.PRODUCT_ID
    ,coalesce(B.PQ, 0) AS PQ
FROM staff_product AS A
LEFT JOIN orders_with_over_20k_product AS B ON A.STAFF_ID = B.STAFF_ID
    AND A.PRODUCT_ID = B.PRODUCT_ID

Upvotes: 0

GMB
GMB

Reputation: 222462

You can do a left join and coalesce():

select
    p.*,
    coalesce(o.pq, 0) pq
from staff_product p
left join orders_with_over_20k_product o
    on o.product_id = p.product_id and o.staff_id = p.staff_id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I think you just want a left join and coalesce():

select sp.*, coalesce(ow.pq, 0) as pq
from staff_product sp left join
     orders_with_over_20k_product ow
     on sp.staff_id = ow.staff_id and sp.product_id = ow.product_id

Upvotes: 1

Related Questions