0

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

1 Answer 1

0

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?

4
  • @ErgestBasha - What output do you want if, instead, #3 has (3,1),(3,2),(3,6)
    – Rick James
    Mar 1, 2023 at 20:07
  • if the use case is #3 with (3,1), (3,2),(3,6) then the finall output should be 3. @RickJames
    – ASD
    Mar 2, 2023 at 8:10
  • 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)
    – ASD
    Mar 2, 2023 at 8:11
  • 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.
    – Rick James
    Mar 2, 2023 at 18:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.