How to use JOIN only when value is not 0

SQL
1

Hello, could anyone help me to reconstruct the bellow query? (please notice it’s only a part of a bigger query). I’d like to return “0” when banner has value 0, and use Join for other cases.

SELECT core.name AS author, core.layout, core.banner, core.ad AS ads
    FROM (
        SELECT al.name, ll.name AS layout, p.banner, CASE p.banner WHEN 0 THEN "0" ELSE (JOIN media AS m ON p.banner = m.file_name) END AS ad
        FROM posts AS p
        JOIN authors_look AS al USING(author_id)
        JOIN layouts_look AS ll USING(layout_id)) AS core
2

Hi GM_sql,

I assume this is the part which you’re wondering of:

CASE p.banner WHEN 0 THEN “0” ELSE (JOIN media AS m ON p.banner = m.file_name) END AS ad

Could you please share the query result that you are looking for? Sorry but I could not visualize why do you need the Join on that part.

3

Exactly, this is the part I’m talking about. By using CASE (or IF) I’d like to return any result, now (when banner = 0) I get empty result. It should be:

author layout banner ads
Tom long 0 “0”
Jane short 33 “bird.jpg”
Lisa long 103 “flower.jpg”

4

Problem already solved. :grinning: