CODEWITHSUNDEEP
selectmysqljoinmultiple-columns
rosh3000
rosh3000

Reputation: 309

join multiple fields to one table

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.

Upvotes: 1

Views: 225

Answers (2)

HLGEM
HLGEM

Reputation: 96640

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.

Upvotes: 0

mechanical_meat
mechanical_meat

Reputation: 169494

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.

Upvotes: 2

Related Questions