Mysql query to compare two rows from the same table

Question

Here I have a table as

pizza_id     toppings
  1          1,2,3,4,5,6,8,9,10
  2          4,6,7,11,12

Since the table should be transposed by each row, I have used below query..,

   select k.pizza_id, k.toppings
   from (select
     pizza_id,
     SUBSTRING_INDEX(SUBSTRING_INDEX(toppings, ',', numbers.n), ',', -1) toppings
   from
     (select 1 n 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 ) numbers INNER JOIN pizza_runner.pizza_recipes
     on CHAR_LENGTH(toppings)
        -CHAR_LENGTH(REPLACE(toppings, ',', ''))>=numbers.n-1
   order by
     pizza_id ) k
     where k.toppings = toppings

I have successfully transposed by each pizza_id as below..,

Now I would like to find the common topping ID's for each pizza_id.., For example (Expected results):

pizza_id         toppings
1                   4
1                   6
2                   4
2                   6

In order to get results as above I have used below query..,

with core as 
(select
     pizza_id,
     SUBSTRING_INDEX(SUBSTRING_INDEX(toppings, ',', numbers.n), ',', -1) toppings
   from
     (select 1 n 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 ) numbers 
      INNER JOIN pizza_runner.pizza_recipes
     on CHAR_LENGTH(toppings)
        -CHAR_LENGTH(REPLACE(toppings, ',', ''))>=numbers.n-1
    ),
    
asd as 
(select
     pizza_id,
     SUBSTRING_INDEX(SUBSTRING_INDEX(toppings, ',', numbers.n), ',', -1) toppings
   from
     (select 1 n 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 ) numbers 
      INNER JOIN pizza_runner.pizza_recipes
     on CHAR_LENGTH(toppings)
        -CHAR_LENGTH(REPLACE(toppings, ',', ''))>=numbers.n-1
    )
    
select pizza_id, toppings
from core as t1
where exists (select pizza_id, toppings
             from asd as t2
             where t1.toppings = t2.toppings 
             group by toppings
             having count(pizza_id) > 1)
group by pizza_id, toppings
order by pizza_id

But unfortunatly I get this results such as ..,

pizza_id    toppings
   1           6
   2           6

I would like to understand why I see only 6, I also want the other common value which is 4 can be visible too. Could anyone suggest me better approach please..., thank you

Answer 1

Commalists, as you found, are a clumsy way to store lists. Instead, use a many-to-many mapping table:

CREATE TABLE toppings (
    pizza_id SMALLINT UNSIGNED NOT NULL,
    topping SMALLINT UNSIGNED NOT NULL,
    PRIMARY KEY(pizza_id, topping),
    INDEX(topping, pizza_id)
 ENGINE=InnoDB;

To get the commalist (like you started with)

SELECT pizza_id, GROUP_CONCAT(topping)
    FROM toppings
    GROUP BY pizza_id;

Since it is unclear whether the question is

• List all pizzas that have each topping. (The sample data does not have enough variety to bring out some issues with this.)

• List all pairs of pizzas that have some toppings in common; and list the common toppings. I would expect the desired output for your sample data to be:

    1,2   4,6
  

• Something else?