CODEWITHSUNDEEP
sqlmysql
philoye
philoye

Reputation: 2590

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) and 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.

Upvotes: 0

Views: 2060

Answers (2)

Jon Black
Jon Black

Reputation: 16559

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;

Upvotes: 2

Andomar
Andomar

Reputation: 238116

Looks like distinct can do the trick:

select  distinct tshirt
,       option_name
,       value_name
from    YourTable

Upvotes: 0

Related Questions