Madushan Perera Madushan Perera - 5 months ago 14
MySQL Question

Get rid of all lev2 elements from lev1

I have a mysql table(cat_with_desc) like below and it consist of categories and parent categories of them.

Ex: Parent of Laptops(4) is Computers(1)

enter image description here

And with the use of that table I manage to display all the hierarchical data like below.

SELECT
CONCAT_WS('-',t1.id,t1.name) AS lev1,
CONCAT_WS('-',t2.id,t2.name) AS lev2,
CONCAT_WS('-',t3.id,t3.name) AS lev3
FROM cat_with_desc AS t1
LEFT JOIN cat_with_desc AS t2 ON t2.parent_cat_id = t1.id
LEFT JOIN cat_with_desc AS t3 ON t3.parent_cat_id = t2.id;


enter image description here

But I dont need lev2 and lev3 data inside lev1 and as well as lev3 data with lev2 column.
EX:4-Laptop,5-Desktop and 6-Touch Screen dont need to be in lev1 column.

This is the final out-come I need to get.

enter image description here

Any suggestions would be appreciable.Thank you. (Dont suggest me to use LIMIT here. :D)

Answer

Just use parent_id for filtering:

SELECT CONCAT_WS('-', t1.id, t1.name) AS lev1,
       CONCAT_WS('-', t2.id, t2.name) AS lev2,
       CONCAT_WS('-', t3.id, t3.name) AS lev3
FROM cat_with_desc t1 LEFT JOIN
     cat_with_desc t2 
     ON t2.parent_cat_id = t1.id LEFT JOIN
     cat_with_desc t3
     ON t3.parent_cat_id = t2.id
WHERE t1.parent_cat_id = 0;