Oracle Join – Get Y or N Value Based on Condition in Another Table

join;oracle

I have to generate simple report from three tables named customer, product, and order. My report should have a column that shows whether the customer ordered that product or not. How could I get the Y or N value based on customer id that exists on order with the product id? Sample table with data is as below:

customer                
customer_id name    address phone   
123 govinda nepal   16910833    
234 arjun   nepal   15546235    

product             
product_id  name    brand   type    price
1   samsung gayalexy s5 samsung smartphone  55000
2   samsung gayalexy s6 samsung smartphone  65000

order               
date    product_id  customer_id     
1/1/2016    1   123     
5/1/2016    2   123

And want the report column as below

cust_id    name      product_id    order_stat

and order_stat should be as Y or N.

Best Answer

Seems like you need all combinations of products and customers (so a CROSS JOIN between the two tables) and then a correlated EXISTS subquery. This could either be a correlated subquery in the SELECT list:

SELECT 
    c.customer_id, 
    p.product_id,
    CASE WHEN EXISTS
              ( SELECT *
                FROM "order"  o
                WHERE o.product_id = p.product_id
                  AND o.customer_id = c.customer_id
              ) 
         THEN 'Y' ELSE 'N'
    END AS order_stat
FROM
    product  p
  CROSS JOIN
    customer  c ;

or a LEFT JOIN to a dummy table with one row (the dual does that in Oracle):

SELECT 
    c.customer_id, 
    p.product_id,
    CASE WHEN d.dummy IS NOT NULL 
         THEN 'Y' ELSE 'N'
    END AS order_stat
FROM
    product  p
  CROSS JOIN
    customer  c 
  LEFT JOIN
    dual  d
      ON EXISTS
              ( SELECT *
                FROM "order"  o
                WHERE o.product_id = p.product_id
                  AND o.customer_id = c.customer_id
              ) ;

Random notes:

  • It's not very good to use reserved keywords in table or column names, like the order. Because you'll have to quote them every time they are used.
Related Question