3

I have spent the last five hours trying to get every product option combination from a table but I'm totally stuck now. I have a table with data like (simplified):

CREATE TABLE `assigned_options` (
  `option_id` int(10) unsigned NOT NULL DEFAULT '0',
  `value_id` int(10) unsigned NOT NULL DEFAULT '0',
);

INSERT INTO `assigned_options` (`value_id`, `option_id`) VALUES
(4, 2),
(3, 2),
(2, 1),
(1, 1),
(5, 3),
(6, 3),
(7, 3);

Say option ID 2 is color with variations red (4) and blue (3), option id 1 size etc....

Is it possible to do this with one MySQL query? I have tried using PHP to make a recurring function to get every possibility but I just can't get it to work.

Any tips GREATLY appreciated. :)

Zoe
  • 27,060
  • 21
  • 118
  • 148
user1757217
  • 31
  • 1
  • 2
  • Can you clarify what do you mean by "this" in your statement "Is it possible to do this" ? Your queries except one small mistake in the `CREATE` one are good, and you can build one large long `INSERT` query to add all possible values at one shot. Am I missing something? – Grzegorz Oct 18 '12 at 18:23
  • 2
    Adding an expected result would help. – Void Ray Oct 18 '12 at 18:25
  • Can you provide an example of what you're trying to do? Generate the assigned_options records, get a list of all possible records? What you're asking for is a little fuzzy... – Ray Oct 18 '12 at 18:26
  • @VoidRay +1 must be my alter ego – Ray Oct 18 '12 at 18:27

3 Answers3

1

Given your table... I'm assuming you want every possible combo of value and option. That's a cross join (a join without any ON or where clause limiting the results):

 SELECT a.value_id, b.option_id 
     FROM assigned_options a 
     JOIN assigned_options b 
     GROUP BY a.value_id, b.option_id

The group by filters out the duplicate results.

Do you have 2 other tables value and option that you want to pull all combinations of?

Ray
  • 40,256
  • 21
  • 101
  • 138
1
select option_id, value_id
from assigned_options
group by option_id, value_id
order by option_id, value_id
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
0

In TSQL you can use a recursive CTE, Can''t remember where I got it, but pretty sweet. Note MYSQL doesn't use "With" option, so it won't work in MySQL

WITH Numbers(N) AS (
                    SELECT N
                    FROM ( VALUES(1), (2), (3), (4), (5), (6)) Numbers(N)),
                        Recur(N,Combination) AS (
                        SELECT N, CAST(N AS VARCHAR(20)) 
                        FROM Numbers


UNION ALL

SELECT n.N,CAST(r.Combination + ',' + CAST(n.N AS VARCHAR(10)) AS VARCHAR(20)) 
FROM Recur r
INNER JOIN Numbers n ON n.N > r.N)



select Combination
from RECUR
ORDER BY LEN(Combination),Combination;
Deno
  • 1