SQL query using outer join and limiting child records for each parent

Question

I'm having trouble figuring out how to structure a SQL query. Let's say we have a User table and a Pet table. Each user can have many pets and Pet has a breed column.

User:

id    |    name
______|________________
1     |    Foo
2     |    Bar

Pet:

id    |    owner_id    |    name    |    breed    |
______|________________|____________|_____________|
1     |        1       |  Fido      |  poodle     |   
2     |        2       |  Fluffy    |  siamese    |

The end goal is to provide a query that will give me all the pets for each user that match the given where clause while allowing sort and limit parameters to be used. So the ability to limit each user's pets to say 5 and sorted by name.

I'm working on building these queries dynamically for an ORM so I need a solution that works in MySQL and Postgresql (though it can be two different queries).

I've tried something like this which doesn't work:

SELECT "user"."id", "user"."name", "pet"."id", "pet"."owner_id", "pet"."name",
  "pet"."breed"
FROM "user"
LEFT JOIN "pet" ON "user"."id" = "pet"."owner_id"
WHERE "pet"."id" IN
  (SELECT "pet"."id" FROM "pet" WHERE "pet"."breed" = 'poodle' LIMIT 5)

So which is your RDBMS? MySQL or Postgres? And which version exactly? — Erwin Brandstetter, Jul 07 '14 at 22:09
Well it's for an ORM so I will eventually need something that works with for any SQL database but for now I'm just focusing on getting MySQL and PostgreSQL working. The same query doesn't need to be used for both databases. I can do a MySQL version and a Postgresql version. As far as which versions of postgresql or mysql, I would say I can limit it to the latest stable versions. — particlebanana, Jul 07 '14 at 22:16

Erwin Brandstetter · Accepted Answer · 2014-07-09T01:06:23.640

In Postgres (8.4 or later), use the window function row_number() in a subquery:

SELECT user_id, user_name, pet_id, owner_id, pet_name, breed
FROM  (
   SELECT u.id AS user_id, u.name AS user_name
        , p.id AS pet_id, owner_id, p.name AS pet_name, breed
        , row_number() OVER (PARTITION BY u.id ORDER BY p.name, pet_id) AS rn
   FROM  "user"    u
   LEFT   JOIN pet p ON p.owner_id = u.id
                    AND p.breed = 'poodle'
   ) sub
WHERE  rn <= 5
ORDER  BY user_name, user_id, pet_name, pet_id;

When using a LEFT JOIN, you can't combine that with WHERE conditions on the left table. That forcibly converts the LEFT JOIN to a plain [INNER] JOIN (and possibly removes rows from the result you did not want removed). Pull such conditions up into the join clause.
The way I have it, users without pets are included in the result - as opposed to your query stub.
The additional id columns in the ORDER BY clauses are supposed to break possible ties between non-unique names.
Never use a reserved word like user as identifier.
Work on your naming convention. id or name are terrible, non-descriptive choices, even if some ORMs suggest this nonsense. As you can see in the query, it leads to complications when joining a couple of tables, which is what you do in SQL.
Should be something like pet_id, pet, user_id, username etc. to begin with.
With a proper naming convention we could just SELECT * in the subquery.

MySQL does not support window functions, there are fidgety substitutes ...

score 0 · Answer 2 · answered Jul 07 '14 at 21:56

SELECT user.id, user.name, pet.id, pet.name, pet.breed, pet.owner_id, 
    SUBSTRING_INDEX(group_concat(pet.owner_id order by pet.owner_id DESC), ',', 5)
    FROM user
    LEFT JOIN pet on user.id = pet.owner_id GROUP BY user.id

Above is rough/untested, but this source has exactly what you need, see step 4. also you don't need any of those " 's.

SQL query using outer join and limiting child records for each parent

2 Answers2

Linked