Home > DeveloperSection > Forums > MySQL Query suggestion
Tanuj Kumar

Total Post:134

Points:940
Posted on    May-02-2013 3:27 AM

 MSSQL Server MSSQL Server 
Ratings:


 1 Reply(s)
 1041  View(s)
Rate this:
Hi All!

I have a MySQL table like this -

ID    NAME     PARENT
== ======== ===========
1     Class     0
2     Math     1
3     Physics   1
4     Cooking 0
5     Italian 4
6     Chinese   4

I want a query that will give me this output -

ID    NAME
== ===================
1      Class
2      Math Class
3      Physics Class
4      Cooking
5      Italian Cooking
6      Chinese Cooking

The parent's name will be appended with the child's name, like surname.

Please help me!
 Any succession will be appreciated! 


AVADHESH PATEL

Total Post:604

Points:4228
Posted on    May-02-2013 8:17 AM

Hi Tanuj!

You can achieve above description as below using LEFT JOIN

LEFT JOIN is needed on this case since there are PARENT that has no match on the ID.

SELECT  a.ID,
        CONCAT(a.Name, ' ', COALESCE(b.name,'')) Name
FROM    TableName a
        LEFT JOIN TableName b
            ON a.Parent = b.ID
SQLFiddle Demo
you can also use CONCAT_WS() so that you can omit COALESCE()

SELECT  a.ID,
        CONCAT_WS(' ', a.Name, b.name) Name
FROM    TableName a
        LEFT JOIN TableName b
            ON a.Parent = b.ID

Don't want to miss updates? Please click the below button!

Follow MindStick