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..,

enter image description here

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

• Luuk: "I would like to understand why I see only 6" That is because of this error: "Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 't2.pizza_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by" (see: DBFIDDLE ) Please do some research on the WHY, and How to avoid this....
• Luuk: A query with all results dbfiddle.uk/9JNOrkfv . but do note that topping=10 is missing.....

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?

• Rick James: @ErgestBasha - What output do you want if, instead, #3 has (3,1),(3,2),(3,6)
• ASD: if the use case is #3 with (3,1), (3,2),(3,6) then the finall output should be 3. @RickJames
• ASD: I am trying the compare the rows with different pizza_id, and find the common values as stated above in my question such as 4,6 for pizza_id (1,2)
• Rick James: I'm lost. "final output should be 3" -- Is that pizza 3, topping 3, 3 rows, or something else. Please add more pizzas and spell out what the result should be.