join multiple fields to one table

Question

I have 2 tables (there are more but un related to question) optionValue and productStock

I want to get the option names from the optionValue table for each option1, option2, option3 (the query below will should help to make more sense)

below is my attempt, the current query it only works if all options are set but is returns null if any option is not set:

    SELECT s.option1, n1.name s.optionName1, 
           s.option2, n2.name s.optionName2,
           s.option3, n3.name s.optionName3
    FROM productStock as s 
    INNER JOIN optionValue n1 on s.option1 = v1.optionValueID
    INNER JOIN optionValue n2 on s.option2 = v2.optionValueID
    INNER JOIN optionValue n3 on s.option3 = v3.optionValueID
    WHERE s.productStockID = 1

I understand why it doesn't work because when the option is null ther is no matches to the optionValue table but im not sure how to fix it (if it is fixable)

I read in a couple of places about using IN or COALESCE but I don't understand how to use them.

Answer 1

It seems like some of your syntax is a little incorrect.

Apart from that you want LEFT OUTER JOIN instead of INNER JOIN.

Visual explanation of SQL joins.

Answer 2

What you really need first it to correct your database design. Anytime you have fields like this: s.option1,s.option2, s.option3

Then what you really need is a child table to store the information. What happens when you need 6 options or 25? This is a very bad database design and will cause no end of problems incuding the inefficent query you now have to write. This is a cancer at the heart of your system and needs to be fixed before anything else is done.