CASE in WHERE statement

[Destramic]

i've read and seen there are variations of what im trying to achieve but havent seen a exact example...what im trying to do is put a where clause in my query depending on if a set variable has a value

CASE WHEN (@category_id IS NOT NULL)
THEN
LEFT JOIN sub_categories sc ON sc.sub_category_id = i.sub_category_id
JOIN categories c ON c.category_id = sc.category_id AND c.category_id = :category_id
END

is this possible or have i just got the syntax wrong somewhere...thank you guys

[requinix]

Can't do a conditional JOIN like that.

 

Join the tables regardless. Keep in mind that making categories an inner join that a matching row will be required, even if you're JOINing it against a table which is an outer join, so it should probably be a LEFT JOIN as well.

 

If that doesn't do it, what problem are you trying to fix or what are you trying to accomplish?

Edited October 28, 2014 by requinix

[Destramic]

well i'm trying to be resourceful with my query's so i dont have to basically write the same one up with a bit of change...but that left join worked great thank you

 

thank you