I need sql for CC order for which conditions are as below:

1. order is G If at least one order line has HAZ = Y

2. order is CC If at least one order line has UD = Y and atleast one of the line has HAZ = Y

for order G sql is as below:

SELECT DISTINCT oh.order_id 
FROM order oh, order ol 
WHERE oh.order_id = ol.order_id
AND ol.sku_id IN (SELECT sku_id FROM dcsdba.sku WHERE haz = 'Y' AND client_id = 'C')
AND oh.client_id = 'C';

CodePudding user response：

If I understand your conditions correctly, the query should be something like:

SELECT DISTINCT order_id
  FROM orders ords
 WHERE client_id = 'C'
   AND EXISTS (SELECT 1
                 FROM dcsdba.sku
                WHERE haz       = 'Y' 
                  AND client_id = 'C'
                  AND sku_id    = ords.sku_id)
   AND EXISTS (SELECT 1
                 FROM dcsdba.sku
                WHERE ud        = 'Y' 
                  AND client_id = 'C'
                  AND sku_id    = ords.sku_id);

what you should be utilizing is the EXISTS clause.