-1

When I have in my DB a category without posts, the resulted select returns rows without an empty category. How to do receiving a table with empty category.

  SELECT
    c.id as category_id,
    pp.id as post_id
  FROM categories c,
  LATERAL
  (SELECT
    id
  FROM posts
  WHERE c.id = posts.category_id
   ORDER BY views DESC
   LIMIT 2) pp
  ORDER BY c.category ASC, pp.id ASC

Current query result:

category_id | post_id
----------------------
1           | 1
1           | 2
3           | 3
3           | 4

I need:

category_id | post_id
----------------------
1           | 1
1           | 2
2           |
3           | 3
3           | 4
Andronicus
  • 25,419
  • 17
  • 47
  • 88
Ulianka Shu
  • 133
  • 1
  • 12

1 Answers1

3

Use left join:

SELECT
    c.id as category_id,
    pp.id as post_id
FROM categories c
LEFT JOIN LATERAL
(SELECT
    id
FROM posts
WHERE c.id = posts.category_id
    ORDER BY views DESC
    LIMIT 2) pp ON TRUE
ORDER BY c.category ASC, pp.id ASC

Notice I have used on true as the joining condition since the lateral join references previous table.

Andronicus
  • 25,419
  • 17
  • 47
  • 88