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"…

Accepted Answer

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 ...

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

Solution

Problem