CODEWITHSUNDEEP
mysqljoinview
user1571670
user1571670

Reputation: 13

Join MySQL Tables: Display All Results From Right Table In One Row Of Left Table

Here is some data in table one (persons)

Person Table
person_ID | Name | Address
        1 | JC   | 303 Main Street
        2 | NM   | 444 Nowhere Drive

And table two (Attribute)

Attribute table
person_ID | attribute
    1     |   dog
    1     |   cat
    2     |  bearded

When I join the tables I get the following view:

person_id | name | attribute
    1     |  JC  |   dog
    1     |  JC  |   cat
    2     |  NM  |  bearded

Their is a duplicate row from the left table. It contains the same person_id etc. I want to combine the first two results into one row. Like this:

person_id | name | attribute | attribute
    1     |  JC  |   dog          cat
    2     |  NM  |  bearded       NULL

or this:

person_id | name | attribute 
    1     |  JC  |  dog, cat
    2     |  NM  |  bearded

Here is some of the code I have tried:

SELECT person.*,Attribute.att FROM `person`,`Attribute` 
WHERE person.`person_id` = Attribute.person_id 
AND name = "" group by person_id

Left Join

Select * from person AS P
LEFT JOIN Attribute AS N
ON P.person_id = N.person_id

Also tried inner join.

Upvotes: 1

Views: 659

Answers (1)

John Girata
John Girata

Reputation: 2101

MySQL has a function GROUP_CONCAT that lets you concatenate rows into a string.

Something like this should work:

SELECT person.*, (
    SELECT GROUP_CONCAT(attribute SEPARATOR ',')
    FROM Attribute
    WHERE person.id = Attribute.person_id
) as attributes
FROM `person`

I don't have MySQL on this machine, so I apologize if there are minor mistakes.

Upvotes: 1

Related Questions