Join one row to multiple rows in another table

Question

I have a table to entities (lets call them people) and properties (one person can have an arbitrary number of properties). Ex:

People

Name  Age -------- Jane  27 Joe   36 Jim   16 

Properties

Name   Property ----------------- Jane   Smart Jane   Funny Jane   Good-looking Joe    Smart Joe    Workaholic Jim    Funny Jim    Young 

I would like to write an efficient select that would select people based on age and return all or some of their properties.

Ex: People older than 26 Name Properties Jane Smart, Funny, Good-looking Joe Smart, Workaholic 

It's also acceptable to return one of the properties and total property count.

The query should be efficient: there are millions of rows in people table, hundreds of thousands of rows in properties table (so most people have no properties). There are hundreds of rows selected at a time.

Is there any way to do it?

Answer 1

Use:

   SELECT x.name,           GROUP_CONCAT(y.property SEPARATOR ', ')      FROM PEOPLE x LEFT 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.