SQL - 如果不为null，则选择不同表中的列

Question

I am trying to make a "recipe" system inside a game. The player can own a company and craft items in there.

I currently fetch the recipes per company type but I don't know how to write the query in a way that I can also fetch the item names and images if the item_id is not empty.

This is working:

SELECT a.recipe_id, 
        a.item1_id, 
        a.item1_uses,
        a.item2_id, 
        a.item2_uses, 
        a.item3_id, 
        a.item3_uses, 
        a.item4_id, 
        a.item4_uses, 
        a.item5_id, 
        a.item5_uses, 
        a.newitem_id, 
        a.newitem_uses, 
        a.craft_description, 
        a.craft_button
    FROM
        company_recipes AS a, 
        company_types AS b
    WHERE
        a.type_id = b.type_id 
    AND
        b.type_id = '".$type."'; 
    "

A recipe can contain for example two items needed to craft something new, but it could also be 5. So if it's only 2, I only want to fetch the img, name of these 2 and the rest can be skipped.

I have a different table store_items that contains the img and name of the item. I was thinking something along the lines of an IF ELSE or CASE WHEN inside the query, but I'm not sure how I'd do that.

Something like: SELECT c.img, c.name FROM store_items AS c IF a.item1_id is not NULL.

I feel like I'm close to the solution, but missing the last step.

Answer 1

Thanks for the tips @jarlh, I've changed the code and came to this result. If you have any more tips to do it better I'm happy to listen. (I'm still a junior and thought myself by trial and error, so I might not have the best solutions at times... Which is why tips are highly appreciated).

SELECT cr.recipe_id, 
        cr.item1_id, 
        cr.item1_uses,
        si1.name,
        si1.img,
        cr.item2_id, 
        cr.item2_uses,
        si2.name,
        si2.img,
        cr.item3_id, 
        cr.item3_uses, 
        si3.name,
        si3.img,
        cr.item4_id, 
        cr.item4_uses,
        si4.name,
        si4.img, 
        cr.item5_id, 
        cr.item5_uses,
        si5.name,
        si5.img, 
        cr.newitem_id, 
        cr.newitem_uses,
        si_new.name,
        si_new.img,
        cr.craft_description, 
        cr.craft_button
    FROM
        company_recipes AS cr
        INNER JOIN company_types AS ct ON cr.type_id = ct.type_id
        LEFT JOIN store_items AS si1 ON cr.item1_id = si1.item_id
        LEFT JOIN store_items AS si2 ON cr.item2_id = si2.item_id
        LEFT JOIN store_items AS si3 ON cr.item3_id = si3.item_id
        LEFT JOIN store_items AS si4 ON cr.item4_id = si4.item_id
        LEFT JOIN store_items AS si5 ON cr.item5_id = si5.item_id
        LEFT JOIN store_items AS si_new ON cr.newitem_id = si_new.item_id
    WHERE
        ct.type_id = '".$type."';

I'm basically fetching everything now and handle the NULLs in the php code now.