MySQL permutation

Question

I have two tables. One has products, and the other has packages that come with it. I need to find out SQL, which allows me to find all the combinations in which I can sell a product with additional features.

Products Name ID Bench 1 Extra Name ID Parent ID QTY undershelf 1 1 1 overshelf 2 1 1 wheels 3 1 1 

I need and output a table that shows the whole combination in which I can sell the product:

 Bench Bench + undershelf Bench + undershelf + overshelf Bench + overshelf Bench + wheels bench + wheels + overshelf and so one. 

Answer 1

Each add-on can be bundled or not, which makes it binary. The way to visualize the combination is to create a word with a bit for each additional, 1 means that the additional list is in the list, 0 means that it is not. For example, Bench + undershelf + overshelf is 110 (or 011 if the binary string is read in reverse order)

Generating each combination of n bits will give each combination of n additions, it will also give each number from 0 to 2^n - 1 .

We can come back from here:
1. generate a list of numbers from 0 to 2^n - 1 ;
2. convert the number to binary to display a combination of additional functions
3. map each bit to an additional
4. Combine the names of additional functions in the beam description.

 SELECT CONCAT(b.Name , COALESCE(CONCAT(' + ' , GROUP_CONCAT(x.Name SEPARATOR ' + ')) , '')) Combination FROM (SELECT p.Name, p.id , LPAD(BIN(uN + tN * 10), e.Dim, '0') bitmap FROM Products p CROSS JOIN (SELECT 0 N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) u CROSS JOIN (SELECT 0 N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t INNER JOIN (SELECT COUNT(1) Dim , `Parent ID` pID FROM Extra) E ON e.pID = p.ID WHERE uN + tN * 10 < Pow(2, e.Dim) ) B LEFT JOIN (SELECT @rownum := @rownum + 1 ID , `Parent ID` pID , Name FROM Extra , (Select @rownum := 0) r) X ON x.pID = b.ID AND SUBSTRING(b.bitmap, x.ID, 1) = '1' GROUP BY b.Name, b.bitmap 

this query will work for up to six additional functions, then it will need another table of numbers (one digit every three additions).

How it works

Subquery E counts the number of additional functions, this is used in C to restrict the elements generated by the tables of digits u and t (unit and tens) to 2 ^ dim.

The number is converted to binary code on BIN(uN + tN * 10) , and then filled with "0" by the number of elements, generating a combination bitmap.

To use the generated bitmap, each add-on needs a fake identifier that will correspond to the position in it, which means subquery X

Two JOIN subqueries are edited by the nth char of the bitmap: if char is 1, then the extension is included, LEFT connected so as not to lose the product without additional functions.

Answer 2

I cannot think of any ingenious way to do this in mysql, but it is very easy in a scripting language. Here in PHP:

 <?php $extra = array('undershelf', 'overshelf', 'sheels'); $possible_combinations = pow(2, count($extra)); for ($i = 0; $i < $possible_combinations; $i++) { $combo = array('Bench'); foreach ($extra as $j => $item) { if ($i & pow(2, $j)) { $combo[] = $item; } } echo implode(' + ', $combo) . "\n"; } 

prints

 Bench Bench + undershelf Bench + overshelf Bench + undershelf + overshelf Bench + sheels Bench + undershelf + sheels Bench + overshelf + sheels Bench + undershelf + overshelf + sheels 

Answer 3

Perhaps completely in MySQL, although not easy. This example can handle up to 5 "extra features" and is easily extensible for more:

 CREATE TABLE products (name varchar(100), id int primary key); INSERT INTO products (name, id) VALUES ('Bench', 1); CREATE TABLE extra (name varchar(100), id int primary key, parent_id int references products.id, qty int); INSERT INTO extra (name, id, parent_id, qty) VALUES ('undershelf', 1, 1, 1), ('overshelf', 2, 1, 1), ('wheels', 3, 1, 1); CREATE TABLE boolean_values (x boolean); INSERT INTO boolean_values VALUES (TRUE), (FALSE); CREATE VIEW product_extras_interim_vw AS SELECT p.id product_id, p.name product_name, e.id extra_id, e.name extra_name, x FROM products p JOIN extra e ON (e.parent_id = p.id) CROSS JOIN boolean_values; SELECT DISTINCT a.product_name , CASE WHEN ax THEN CONCAT(' + ', a.extra_name) END extra1 , CASE WHEN bx THEN CONCAT(' + ', b.extra_name) END extra2 , CASE WHEN cx THEN CONCAT(' + ', c.extra_name) END extra3 , CASE WHEN dx THEN CONCAT(' + ', d.extra_name) END extra4 , CASE WHEN ex THEN CONCAT(' + ', e.extra_name) END extra5 FROM product_extras_interim_vw a LEFT JOIN product_extras_interim_vw b ON ( a.product_id = b.product_id AND b.extra_id > a.extra_id AND ax ) LEFT JOIN product_extras_interim_vw c ON ( a.product_id = c.product_id AND c.extra_id > b.extra_id AND bx ) LEFT JOIN product_extras_interim_vw d ON ( a.product_id = d.product_id AND d.extra_id > c.extra_id AND cx) LEFT JOIN product_extras_interim_vw e ON ( a.product_id = e.product_id AND e.extra_id > d.extra_id AND dx) ORDER BY product_name, extra1, extra2, extra3, extra4, extra5; 

Output:

 Bench Bench + overshelf Bench + overshelf + wheels Bench + undershelf Bench + undershelf + overshelf Bench + undershelf + overshelf + wheels Bench + undershelf + wheels Bench + wheels