To Get Structured Data mysql

Question

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

Please provide more information.. Do you want to know how to write a query to fetch values or how to create the tables? — Karthik N, Feb 08 '16 at 06:52
how to write the query for this....the above is my table and below is how to display the data — Vamshi Goli, Feb 08 '16 at 06:54
Pls show us what you have tried to achive the exoected output! — Shadow, Feb 08 '16 at 06:56
Possible duplicate of [How to create a MySQL hierarchical recursive query](http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) — Shadow, Feb 08 '16 at 06:57
i am not getting the logic brother so that's the reason i have kept my question here — Vamshi Goli, Feb 08 '16 at 07:00

Tim Biegeleisen · Accepted Answer · 2016-02-08T14:25:16.477

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

To Get Structured Data mysql

1 Answers1