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

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.

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.

Without seeing more info its had to see what you are trying achieve but you could start by using the the users inpute of the game to determine what data is first required before futher filtering. Try this:

Declare @Value int set @Value = @User_input --- uses what ever the game user will

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
    --- you can insert more columns but i stopped here as i dont know what data you have in the other tables.
FROM
    company_recipes a  
    INNER JOIN company_types b ON a.type_id = b.type_id
    INNER JOIN store_items c ON c.type_id = b.type_id
WHERE 
    b.type_id = @Value; --- '".$type."';