I am trying to retrieve outputs with multiple ‘and’ statements. My code works fine until i add a particular ‘and’ statement.
This is the code that i am using
select OC.CUSTOMER_ID,OC.CUSTOMER_FNAME,OC.CUSTOMER_LNAME,OH.PAYMENT_MODE,OH.ORDER_ID,SUM(OI.PRODUCT_QUANTITY) AS PRODUCT_QUANTITY,OH.ORDER_STATUS
FROM online_customer AS OC JOIN order_header AS OH
ON OC.CUSTOMER_ID=OH.CUSTOMER_ID
JOIN order_items AS OI
ON OH.ORDER_ID=OI.ORDER_ID
WHERE OH.ORDER_STATUS LIKE '%Shipped%'AND
OH.PAYMENT_MODE LIKE '%CREDIT CARD%' AND OH.PAYMENT_MODE LIKE '%NET BANKING%' AND
OH.ORDER_ID IN (SELECT ORDER_ID FROM ORDER_ITEMS GROUP BY ORDER_ID HAVING SUM(PRODUCT_QUANTITY)>10)
GROUP BY ORDER_ID;
The issue pop’s up when i add this particular line
OH.PAYMENT_MODE LIKE '%CREDIT CARD%' AND OH.PAYMENT_MODE LIKE '%NET BANKING%'
when i add this line only the table heads are derived output 1.
But when i remove that statement the output work’s fine output 2.
>Solution :
"AND" means "both of these conditions have to be true for the same row".
There is no row where OH.PAYMENT_MODE contains both ‘CREDIT CARD’ and ‘NET BANKING’ so the condition is not true for any rows.
You want to look for rows where either of the conditions is true, so need an "OR". However, always be careful mixing "AND" and "OR"; the best idea is to use brackets, so ... AND ( OH.PAYMENT_MODE LIKE '%CREDIT CARD%' OR OH.PAYMENT_MODE LIKE '%NET BANKING%' ) AND ...