if else use in mysql where clause

Question

I have this query with inner join. Currently this returns data when product_id exists in the coupon_restrict table. What I want to do is that if product_id doen't event exist then it should return the data from coupon table.

SELECT c.public_private, c.coupon_code, c.coupon_amount, c.coupon_type, 
c.coupon_zone_restriction, c.coupon_expire_date, c.is_stash
FROM coupons c, 
coupon_restrict cr, 
products p
WHERE 
c.coupon_type = 'P' 
AND (c.public = 1 OR c.public_private = 1) 
AND c.coupon_id = cr.coupon_id 
AND DATEDIFF(c.coupon_expire_date, NOW()) > 0
AND cr.product_id IN (1808)
AND cr.product_id = p.products_id
AND p.products_quantity > 0
AND cr.coupon_restrict = 'N'

Answer 1

You need left join for your coupon_restrict and products if you need the data from coupons table whether their association exists in other table or not

SELECT c.public_private, c.coupon_code, c.coupon_amount, c.coupon_type, 
c.coupon_zone_restriction, c.coupon_expire_date, c.is_stash
FROM coupons c
LEFT JOIN coupon_restrict cr ON c.coupon_id = cr.coupon_id  
                             AND cr.coupon_restrict = 'N'
                             AND cr.product_id IN (1808)
LEFT JOIN products p ON cr.product_id = p.products_id 
                     AND p.products_quantity > 0
WHERE c.coupon_type = 'P' 
AND (c.public = 1 OR c.public_private = 1) 
AND DATEDIFF(c.coupon_expire_date, NOW()) > 0

Also do move your where filters for joined tables in on clause