MySQL, conditional WHERE clause

Question

Here's my statement:

SELECT
    a.size AS 'size_id',
    b.size,
    a.stock,
    a.id AS 'stock_id',
    IFNULL(c.quantity,0) as 'cart_qty'
FROM
    stock a
JOIN 
    sizes b ON a.size = b.id
LEFT JOIN
    cart_items c ON a.id = c.stock_id
WHERE
    a.product_id = '{$product['id']}'
AND
    c.cart_id = '$cart_id'
AND
    a.stock > 0
ORDER BY
    b.display_order

In the above, $cart_id might be blank, or it might have no matches in the 'cart_items' table. How do I rephrase the above so that if there are no matches in cart_items, I still get the results from stock that match product_id? But if there are matches in cart_items then I get the additional column (cart_qty)?

If you want to get the product info whatever it's in the cart, why are you limiting the result by cart_id? Just remove the AND c.cart_id = $cart_id — Keiran Tai
– Keiran Tai, Commented Feb 2, 2016 at 12:38
@Keiran Tai this runs when the standard product page is loaded. So IF the product is already in the user's shopping cart, I want to know the quantities. — daninthemix
– daninthemix, Commented Feb 2, 2016 at 12:44

Giorgos Betsos · Accepted Answer · 2016-02-02 12:24:29Z

2

Move the predicate to ON clause:

LEFT JOIN
    cart_items c ON a.id = c.stock_id AND c.cart_id = '$cart_id'

This way, if there are no matches in cart_items, you still get the results from stock that match product_id. Otherwise, if there are matches in cart_items then the additional column cart_qty is properly filled.

answered Feb 2, 2016 at 12:24

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Juan Carlos Oropeza · Accepted Answer · 2016-02-02 12:24:19Z

0

I'm not sure if you can do both. But first one in case $cart_Id is null you add

AND ($cart_Id is null or c.cart_id = '$cart_id')

answered Feb 2, 2016 at 12:24

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Collectives™ on Stack Overflow

MySQL, conditional WHERE clause

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related