The fastest way to create string combinations

Question

We have a table of plans, and each plan has many services. We would like a quick way to find combinations of plans that do not contain duplicate services, but since the combinations contain certain services.

eg. table of plans

id | service_1 | service_2 | ...
---------------------------------
1  |   true    |  true     | ...
2  |   true    |  false    | ...
3  |   false   |  true     | ...

eg. valid combinations containing service_1 and service_2

UPDATE

If there were 2 services, and I needed both of them, we would combine up to two lines (or plans), since they could contain at least 1 service in each.

id | service_1 | service_2 | id | service_1 | service_2 |
---------------------------------------------------------
1  |   true    |  true     |NULL|    NULL   |    NULL   |
2  |   true    |  false    | 3  |    false  |    true   |

UPDATE

Currently, it works on its own, combining with aggressive trimming or strings. The request is dynamically generated depending on the number of services. It creates permutations under valid join conditions, which makes publishing impossible.

^ .

, .

Answer 1

,

DROP TABLE IF EXISTS plan;
CREATE TABLE plan (id int, service1 bool, service2 bool, service3 bool);
INSERT INTO `plan` (`id`, `service1`, `service2`, `service3`) VALUES (1, 1, 0, 0);
INSERT INTO `plan` (`id`, `service1`, `service2`, `service3`) VALUES (2, 0, 1, 0);
INSERT INTO `plan` (`id`, `service1`, `service2`, `service3`) VALUES (3, 1, 1, 1);
INSERT INTO `plan` (`id`, `service1`, `service2`, `service3`) VALUES (4, 1, 0, 1);
INSERT INTO `plan` (`id`, `service1`, `service2`, `service3`) VALUES (5, 0, 0, 1);

select *
from plan A
left join (
    select id, service1, service2, service3 from plan
    union all
    select null, null, null, null) B on B.id > A.id or B.id is null
left join (
    select id, service1, service2, service3 from plan
    union all
    select null, null, null, null) C on C.id > B.id or C.id is null
WHERE (A.service1 + A.service2 + A.service3)
  AND (A.service1 + ifnull(B.service1,0) + ifnull(C.service1,0)) = 1
  AND (A.service2 + ifnull(B.service2,0) + ifnull(C.service2,0)) = 1
  AND (A.service3 + ifnull(B.service3,0) + ifnull(C.service3,0)) = 1

id | service1 | service2 | service3 | id | service1 | service2 | service3 | id | service1 | service2 | service3
1 | 1 | 0 | 0 | 2 | 0 | 1 | 0 | 5 | 0 | 0 | 1
1 | 1 | 0 | 0 | 5 | 0 | 0 | 1 | 2 | 0 | 1 | 0
2 | 0 | 1 | 0 | 4 | 1 | 0 | 1 | NULL | NULL | NULL | NULL
2 | 0 | 1 | 0 | NULL | NULL | NULL | NULL | 4 | 1 | 0 | 1
3 | 1 | 1 | 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL

Answer 2

, , "", . , - , .

With RawData As
    (
    Select 1 As id, 1 As service_1, 1 As service_2
    Union All Select 2, 1, 0
    Union All Select 3, 0, 1
    )
    , BinData As
    (
    Select A.id, A.service_1, A.service_2
        , A.service_1 * 2 + A.service_2 As Bin
    From RawData As A
    )
Select *
From BinData As F1
    Left Join BinData As F2
        On F2.id <> F1.id
            And F1.Bin & F2.Bin = 0
Order By F1.id

, id = 3. , id = 3 "" id = 2, .

, , , "".

, , , , , , , , , 1. , , , XOR'd :

With RawData As
    (
    Select 1 As id, 1 As service_1, 1 As service_2
    Union All Select 2, 1, 0
    Union All Select 3, 0, 1
    )
    , BinData As
    (
    Select A.id, A.service_1, A.service_2
        , A.service_1 * 2 + A.service_2 As Bin
    From RawData As A
    )
Select *, F1.Bin ^ F2.Bin
From BinData As F1
    Left Join BinData As F2
        On F2.id <> F1.id
            And F1.Bin ^ F2.Bin = 3
Order By F1.id

, id = 3 , id = 3 id = 2, .