Join one row to multiple rows in another table
- by Ghostrider
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?