Retrieve multi-level data in a single query - MySQL Group Concat

user_14637980 0
Viewed 36

I have a multi-level data in a single table as seen below.

  • The category/brand is identified by having null parent id.
  • The Product is identified by having parent id corresponding to top level category (mid).
  • Each product can also have a subtype or variety of the basic version - this is identified by having a sub column corresponding to the mid of the basic product.
mid parent sub org item price dsc1
1 1 Apple
2 1 1 iPhone $1000 (Description)
3 1 2 1 iPhone Plus $1200 (Description)
4 1 1 iMac Basic $600 (Description)
5 1 4 1 iMac 1TB $900 (Description)

The desired output is on the basic product level (not null parent + null sub) and it has to encompass a top level category as well as all sub varieties and descriptions:

Apple $1000 iPhone $1200 iPhone Plus (Description)
Apple $600 iMac Basic $900 iMac 1TB (Description)

I am trying everything possible with SQL but I cannot manage to combine everything in a single query - I don't want to first check for the category and then do another query for the individual products.

SELECT *, IF(parent IS NULL, item, mid) AS topcategory FROM table where org = "1";

I was also trying with GROUP_CONCAT without any luck: https://phpize.online/?phpses=5c149faca3e15d3bcd7f4c0e437c28ff&sqlses=c40160db7e24f450d3055397db86aeda&php_version=php8&sql_version=mysql57

mysql php
1 Answers

I mean you can use next query:

SELECT 
    `parent`.`item` `parent`,
    `sub`.`item` `sub`,
    `sub`.`price` `sub_price`,
    `menu`.`item` `menu`,
    `menu`.`price` `menu_price`,
    `menu`.`dsc1`
FROM `menu`
JOIN `menu` `sub` on `sub`.`mid` = `menu`.`sub`
JOIN `menu` `parent` on `parent`.`mid` = `menu`.`org`;

MySQL fiddle

Result

+========+=========+===========+=============+============+======+
| parent | sub     | sub_price | menu        | menu_price | dsc1 |
+========+=========+===========+=============+============+======+
| Apple  | iPhone  | $1000     | iPhone Plus | $1221      | (HI) |
+--------+---------+-----------+-------------+------------+------+
| Apple  | iPhone  | $1000     | iPhone Red  | $1600      | (HI) |
+--------+---------+-----------+-------------+------------+------+
user_33993560
Related