Vamshi Goli February 2016

To Get Structured Data mysql

i need a requirement as below can anyone please assist me depending on the parent_id and id the relation

 id | category_name | parent_id 

 1    Electronics         0        

 2    computer            1

 3    ram                 2

 4    Fashion             0

 5    tshirt              4

 6     round neck         5

i need a requirement as below

category name     |      sub category     |      sub sub category  | 

Electronics                NA                        NA

Electronics                computer                  NA

Electronics                computer                  ram

Fashion                    NA                        NA

Fashion                    tshirt                    NA

Fashion                    tshirt                   round neck

please help

Answers


Tim Biegeleisen February 2016

After a few hours of pain and suffering, I arrived at the following query. Note that the links given as comments above did nothing for me.

SELECT t.* FROM
(
    SELECT COALESCE(c3.category_name, c2.category_name, c1.category_name, 'NA') AS `category name`,
        CASE WHEN c3.category_name IS NOT NULL
            THEN c2.category_name
            ELSE
                CASE WHEN c2.category_name IS NOT NULL
                    THEN c1.category_name
                    ELSE 'NA'
                END 
        END AS `sub category`,
        CASE WHEN c3.category_name IS NOT NULL
            THEN c1.category_name
            ELSE 'NA'
        END AS `sub sub category`
    FROM category c1 LEFT JOIN category c2
        ON c1.parent_id = c2.id
    LEFT JOIN category c3
        ON c2.parent_id = c3.id ) t
ORDER BY t.`category name`,
    CASE WHEN t.`sub category` = 'NA' THEN NULL ELSE t.`sub category` END,
    CASE WHEN t.`sub sub category` = 'NA' THEN NULL ELSE t.`sub sub category` END

Follow the link below for a running demo:

SQLFiddle

Post Status

Asked in February 2016
Viewed 2,036 times
Voted 11
Answered 1 times

Search




Leave an answer