How to get the cartesian product in MySQL for a single table

Disclaimers first: I'm dealing with a legacy database with a pretty bizarre schema. Plus, I'm a complete SQL noob, so that's not helping either.

Basically, I have a table that has product variations. A good example might be t-shirts. The "generic" t-shirt has a product id. Each type of variation (size, color) has an id. Each value of the variation (red, small) has an id.

So table looks like:

 +----+----------+-----------+-------------+----------+------------+
 | id | tshirt   | option_id | option_name | value_id | value_name |
 +----+----------+-----------+-------------+----------+------------+
 | 1  | Zombies! | 2         | color       | 13       | red        |
 | 1  | Zombies! | 2         | color       | 24       | black      |
 | 1  | Zombies! | 3         | size        | 35       | small      |
 | 1  | Zombies! | 3         | size        | 36       | medium     |
 | 1  | Zombies! | 3         | size        | 56       | large      |
 | 2  | Ninja!   | 2         | color       | 24       | black      |
 | 2  | Ninja!   | 3         | size        | 35       | small      |
 +----+----------+-----------+-------------+----------+------------+

I want to write a query that retrieves the different combinations for a given product.

In this example, the Zombie shirt comes in Red/Small, Red/Medium, Red/Large, Black/Small, Black/Medium, and Black/Large (six variations). The Ninja shirt just has the one variation: Black/Small.

I believe this is the cartesian product of size and color.

Those ids are really foreign keys to other tables, so those names/values aren't but wanted to include for clarity.

The number of options can vary (not limited to two) an开发者_运维知识库d the number of values per option can vary as well. Ultimately, the numbers are likely to small-ish for a given product so I'm not worried about millions of rows here.

Any ideas on how I might do this?

Thanks, p.

try this:

select 
 f.id,
 f.tshirt,
 color.option_id as color_option_id,
 color.option_name as color_option_name,
 color.value_id as color_value_id,
 color.value_name as color_value_name,
 size.option_id as size_option_id,
 size.option_name as size_option_name,
 size.value_id as size_value_id,
 size.value_name as size_value_name
from 
 foo f
inner join foo color on f.id = color.id and f.value_id = color.value_id and color.option_id = 2 
inner join foo size on f.id = size.id and size.option_id = 3
order by 
 f.id, 
 color.option_id, color.value_id, 
 size.value_id, size.value_id;

Looks like distinct can do the trick:

select  distinct tshirt
,       option_name
,       value_name
from    YourTable