Mysql Query JOIN Query

Question

I have three tables. Table A has the option name (i.e. Color, Size). Table B has option value name (i.e Blue, Red, Black, Etc.). Table C establishes the relationship by putting option name id and option name value id together in a row.

My query needs to show the names of the values and and options as opposed to the id number. I can do A and B one at a time and get a list of value and options names, but since the value and options are related I want a combined list. For Example.

Color - Blue Color - Red Size - Big Size - Small

So my question is how to join these two queries to get a combined result like above.

SELECT products_options.products_options_name FROM products_options
LEFT JOIN products_options_values_to_products_options ON products_options_values_to_products_options.products_options_id=products_options.products_options_id

SELECT products_options_values.products_options_values_name FROM products_options_values
LEFT JOIN products_options_values_to_products_options ON products_options_values_to_products_options.products_options_values_id=products_options_values.products_options_values_id

Denis de Bernardy · Accepted Answer · 2011-05-25 10:33:14Z

1

You need two joins, which will look like this:

select product_options.product_id,
       option_names.name_id,
       option_names.name,
       option_values.value_id,
       option_values.name
from product_options
join option_names using (option_id)
join option_values using (option_id)
where product_options.product_id = :product_id

It won't combine them together for an immediate insert in your variations table, but this is the best you'll do in MySQL.

edited May 25, 2011 at 10:33

answered May 25, 2011 at 10:06

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Busilinks Over a year ago

Thanks. This worked with one minor correction. join option_names using (name_id) join options_values using (value_id). I also omitted the where statement.

Collectives™ on Stack Overflow

Mysql Query JOIN Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related