MySQL concat multiple row into one column using concat but without GROUP

Question

I have table of Person having multiple Attributes, and I want to join them so I get result like this:

First_Name | Last_Name | Attribute
======================================================
John       | Smith     | Cool, Tall, Brown Hair
Steve      | Bob       | Impatient, Short, Blonde Hair
Hector     | Hector    | Groovy, Funny, Black Hair

The tables are:

Person {id, first_name, last_name}
Attribute {id, description}
Ref_Attribute {id, ref_person_id, ref_attribute_id}

I know I can use GROUP_CONCAT and at the end use GROUP BY person.id, but I don't want to use GROUP BY, because I need to join with another table which I need to separate them as different row. The table to join is House. Which means, if a Person have multiple house, then it will give:

First_Name | Last_Name | Attribute                     | House
=======================================================================
John       | Smith     | Cool, Tall, Brown Hair        | Bourke St. Ave
Steve      | Bob       | Impatient, Short, Blonde Hair | Flinders St.
Hector     | Hector    | Groovy, Funny, Black Hair     | Crown Golf

Is there any way I can join and get the result without GROUP BY?

Rahul · Accepted Answer · 2015-10-09 10:08:58Z

1

You can get the grouping first and then perform a JOIN like

SELECT p.first_name, p.last_name, xx.Attributes, h.Housees
FROM Person p JOIN
(
SELECT id, GROUP_CONCAT(description) AS Attributes
FROM Attribute 
GROUP BY id ) xx ON p.id = xx.id
JOIN House h on p.id = h.id;

answered Oct 9, 2015 at 10:08

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mark Guinn · Accepted Answer · 2015-10-09 10:16:06Z

1

If the issue is that a person can have 1 or many houses and you want each house in it's own row, you can just group by Person.id and also House.id.

SELECT p.first_name, p.last_name, GROUP_CONCAT(a.Description SEPARATOR ', ') as attributes, h.House
FROM Person p
LEFT JOIN Attributes a ON p.id = a.person_id
INNER JOIN Houses h on p.id = h.person_id
GROUP BY p.id, h.id

This is functionally equivalent to Rahul's answer above (it would be if you changed the left join above to an inner but I don't think you want to do that). You'd have to do some profiling to see which is faster.

answered Oct 9, 2015 at 10:16

Mark Guinn

7193 silver badges8 bronze badges

1 Comment

yodann Over a year ago

hi Mark, your answer work well too. but i will go with Rahul as I have coded that way. but thanks.

Collectives™ on Stack Overflow

MySQL concat multiple row into one column using concat but without GROUP

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related