CODEWITHSUNDEEP
mysqlsql
kjones1876
kjones1876

Reputation: 762

How to merge the rows of a second table into 1 field

I've been trying to produce a query that will search table1, and then CONCAT the all the values of table2.column1 where table1.id = table2.owner

People
 name | id
-------------
 tim  | 1
 jill | 2
 john | 3


Dogs
name | owner
--------------
 a   | 1
 b   | 1
 c   | 2
 d   | 2

Using the following table i need a query that would output

 name | dogs
 -----------
 tim  | a, b
 jill | c, d
 john | NULL (or a blank text or just so long as the name john still shows)

I have spent a few hours and really cant do it. Some form of mash between OUTER JOIN, and group_concat(), i think. But i didnt really get close to my answer.

Thank you for all help!

Upvotes: 1

Views: 56

Answers (2)

Taryn
Taryn

Reputation: 247670

You will want to use GROUP_CONCAT and a GROUP BY

SELECT p.name, GROUP_CONCAT(d.name)
FROM   people p
LEFT JOIN dogs d 
     ON p.id = d.owner
GROUP BY p.name

see SQL Fiddle with Demo

Upvotes: 3

John Woo
John Woo

Reputation: 263693

I guess you looking for GROUP_CONCAT in MySQL.

SELECT  a.name, GROUP_CONCAT(b.name) dogsName
FROM    People a
        LEFT JOIN Dogs b
          ON a.id = b.owner
GROUP BY a.name

SQLFiddle Demo

Upvotes: 2

Related Questions