CODEWITHSUNDEEP
mysqlsql
Roel Veldhuizen
Roel Veldhuizen

Reputation: 4723

Concat child records in parent SELECT

To my best knowledge of MySQL this not a possible thought in this case would be helpful.

We want to select a record in a parent table and concat (something like that) the child rows in the select. Here in obviously wrong MySQL but to illustrate what we want to achieve. 

SELECT parentattr, 
       CONCAT (
               SELECT name 
               FROM child 
               WHERE child.parentId = parent.id)) as allchildernames
FROM parent

Upvotes: 0

Views: 2888

Answers (3)

Sefer K
Sefer K

Reputation: 514

try this

SELECT parentattr, GROUP_CONCAT(child.name SEPARATOR ', ') as allchildernames
FROM parent, child 
WHERE child.parentId = parent.id 
GROUP BY parent.id

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35603

You also need a GROUP BY, plus you need to specify exactly which name is being concatenated.

SELECT parentattr1, parentattr2, GROUP_CONCAT(c.name ORDER By c.name)
FROM parent p
LEFT JOIN child c ON parentId = c.id
GROUP BY parentattr1, parentattr2

Upvotes: 3

Arun
Arun

Reputation: 951

Try using GROUP_CONCAT (), here are examples.

You also have an extra ) after parent.id.

Upvotes: 0

Related Questions