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?

Are you looking to get a comma separated list of properties? Also, do you need to specify which properties you would like to see? I.e. "People older than 26 who are funny and good looking" — , May 23 '10 at 18:11

score 34 · Accepted Answer · edited Jul 22 '20 at 17:27

34

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.

edited Jul 22 '20 at 17:27

waterproof

4,943
5
30
28

answered May 23 '10 at 18:09

OMG Ponies

325,700
82
523
502

1

@meriton: I'd like have both columns in the PROPERTIES table as the primary key. – OMG Ponies May 23 '10 at 18:24
Right, that is even better than just blindly following the "every foreign key should have an index" rule :-) – meriton May 23 '10 at 18:36
4

If anyone happens across this question and is using an Oracle DB, there is an equivalent LISTAGG function (since Oracel 11.2 I think): http://docs.oracle.com/cd/E14072_01/server.112/e10592/functions087.htm – CodeClimber Aug 15 '12 at 10:51
@CodeClimber: The question is tagged for MySQL, not Oracle. – OMG Ponies Aug 15 '12 at 13:45
3

@OMGPonies Yep, I'm aware of that. Not trying to answer the question, just help out anyone who ends up here with a similar query on an Oracle DB. – CodeClimber Aug 15 '12 at 20:34
@OMGPonies FWIW, I found it helpful. BTW the link is broken, the new one is https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030 – Sanjay T. Sharma Nov 29 '19 at 08:41

score 4 · Answer 2 · edited May 05 '15 at 09:49

4

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

edited May 05 '15 at 09:49

Bram

2,515
6
36
58

answered May 05 '15 at 09:24

mayur awati

37
1

score 3 · Answer 3 · answered May 23 '10 at 18:11

You can use INNER JOIN to link the two tables together. More info on JOINs.

SELECT *
FROM People P
INNER JOIN Properties Pr
  ON Pr.Name = P.Name
WHERE 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 P
INNER JOIN Properties Pr
  ON Pr.id = P.peopleId
WHERE P.Name = 'Joe'

Join one row to multiple rows in another table

People

Properties

3 Answers3

Linked