Join one row to multiple rows in another table
Use:
SELECT x.name, GROUP_CONCAT(y.property SEPARATOR ', ') FROM PEOPLE xLEFT JOIN PROPERTIES y ON y.name = x.name WHERE x.age > 26 GROUP BY x.name
You want the MySQL function GROUP_CONCAT (documentation) in order to return a comma separated list of the PROPERTIES.property value.
I used a LEFT JOIN rather than a JOIN in order to include PEOPLE records that don't have a value in the PROPERTIES table - if you only want a list of people with values in the PROPERTIES table, use:
SELECT x.name, GROUP_CONCAT(y.property SEPARATOR ', ') FROM PEOPLE x JOIN PROPERTIES y ON y.name = x.name WHERE x.age > 26 GROUP BY x.name
I realize this is an example, but using a name is a poor choice for referencial integrity when you consider how many "John Smith"s there are. Assigning a user_id, being a unique value per user, would be a better choice.
You can use INNER JOIN
to link the two tables together. More info on JOINs.
SELECT *FROM People PINNER JOIN Properties Pr ON Pr.Name = P.NameWHERE P.Name = 'Joe' -- or a specific age, etc
However, it's often a lot faster to add a unique primary key to tables like these, and to create an index to increase speed.
Say the table People
has a field id
And the table Properties
has a field peopleId
to link them together
Then the query would then look something like this:
SELECT *FROM People PINNER JOIN Properties Pr ON Pr.id = P.peopleIdWHERE P.Name = 'Joe'
SELECT x.name,(select GROUP_CONCAT(y.Properties SEPARATOR ', ')FROM PROPERTIES y WHERE y.name.=x.name ) as Properties FROM mst_People x
try this