Reputation: 99

MySQL Query suggestion

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.

Upvotes: 0

Answers (2)

Reputation: 263723

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

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

Upvotes: 2

Reputation: 160853

SELECT t1.ID, CONCAT(t1.NAME, ' ', t2.NAME) AS NAME
FROM your_table t1
INNER JOIN your_table t2 ON t1.PARENT = t2.ID

Upvotes: 0