CODEWITHSUNDEEP
mysql
daninthemix
daninthemix

Reputation: 2570

MySQL, conditional WHERE clause

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)?

Upvotes: 0

Views: 303

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72215

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.

Upvotes: 2

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

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')

Upvotes: 0

Related Questions