Pick up the name of the column by two diff column in single table Thank you guys in advance! i am desperately need to display my data in this way,



My database structure is,



Column idcat, parent_id and child_id is sharing single column name, catname.

I tried to write my own query, 

SELECT idcat, parent_id`, child_id, catname, catDesc, catstat, del_cat 
(SELECT catname WHERE parent_id=idcat FROM categories ) as parent, 
(SELECT catname WHERE child_id=`parent_id FROM categories) as child 
FROM categories


I hope you can help me guys! thank you!
Use aliases like that:

Select a.Column, b.Column
FROM TableA AS a
JOIN TableB AS b on a.TableBId = b.Id


This way the engine knows when you want to use Column from TableA and when from TableB.

It is a good practice - you should always use aliases when writing SQL queries.

The query you are looking for might be:

SELECT c.catname
    ,(
        SELECT c1.catname + coalesce('&gt;' + cast(
                    SELECT c2.child_id
                    FROM categories c2
                    WHERE c2.idcat = c1.child_id
                    ) AS char(50)), '')
        FROM categories c1
        WHERE c.parent_id = c1.idcat
        )
    ,c.catDesc
    ,c.catstat
FROM categories c