How to grab 2 products for each department?

Question

I am trying to grab 2 products for each department. A department has many aisles, an aisle has many products.

Why isn't the LIMIT in the LATERAL working? At the moment, it is returning more that 2 products.

I've got:

SELECT d.id, p.* FROM departments d
JOIN aisles a ON d.id = a.department_id, LATERAL (
  SELECT * FROM products p1 WHERE p1.aisle_id = a.id ORDER BY p1.id ASC LIMIT 2
) p
WHERE a.department_id IN (3,5);

Which returns:

 id | id  |  aisle_id |            name             |  image_filename |         created_at         |         updated_at
----+-----+-----------+-----------------------------+-----------------+----------------------------+----------------------------
  3 | 149 |        11 | Sleek Wool Watch            |  foo.png        | 2015-10-30 10:03:21.107873 | 2015-10-30 10:03:21.107873
  3 |   3 |        12 | Heavy Duty Granite Lamp     |  foo.png        | 2015-10-30 10:03:20.637513 | 2015-10-30 10:03:20.637513
  3 |  88 |        12 | Rustic Marble Shirt         |  foo.png        | 2015-10-30 10:03:20.883394 | 2015-10-30 10:03:20.883394
  3 |  56 |        13 | Enormous Linen Bottle       |  foo.png        | 2015-10-30 10:03:20.786546 | 2015-10-30 10:03:20.786546
  5 |  22 |        24 | Gorgeous Linen Clock        |  foo.png        | 2015-10-30 10:03:20.687868 | 2015-10-30 10:03:20.687868
  5 | 104 |        24 | Synergistic Leather Bag     |  foo.png        | 2015-10-30 10:03:20.933504 | 2015-10-30 10:03:20.933504
  5 |  80 |        25 | Enormous Leather Bag        |  foo.png        | 2015-10-30 10:03:20.860636 | 2015-10-30 10:03:20.860636
  5 | 117 |        25 | Aerodynamic Bronze Gloves   |  foo.png        | 2015-10-30 10:03:21.015299 | 2015-10-30 10:03:21.015299

Shankar · Answer 1 · 2015-11-04T07:01:59.287

I hope you need to mention LEFT OUTER JOIN LATERAL and ON True,which will fix your issue .

SELECT d.id, p.* FROM departments d
JOIN aisles a ON d.id = a.department_id LEFT OUTER JOIN LATERAL (
  SELECT * FROM products p1 WHERE p1.aisle_id = a.id ORDER BY p1.id ASC LIMIT 2
) p
ON True
WHERE a.department_id IN (3,5);

More information here What is the difference between LATERAL and a subquery in PostgreSQL?

How to grab 2 products for each department?

1 Answers1