Question 89019 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL join first result from another table

I have the following tables:

Posts:

| id (pk) | title   |
|---------|---------|
| 1       | title 1 |
| 2       | title 2 |

Images:

| id (pk) | post_id (fk) | display_order | image_path |
|---------|--------------|---------------|------------|
| 1       | 1            | 1             | image1.jpg |
| 2       | 1            | 2             | image2.jpg |
| 3       | 1            | 0             | image3.jpg |
| 4       | 2            | 1             | image4.jpg |
| 5       | 2            | 1             | image5.jpg |
| 6       | 2            | 2             | image6.jpg |

I’d like to retrieve a list of posts each containing the first item in images based on display_order.

The desired result would be like this:

| id | title   | thumb_path |
|----|---------|------------|
| 1  | title 1 | image3.jpg |
| 2  | title 2 | image4.jpg |

How can I do this?

Tags:

2

Answers


  1. 0

    You can get only one row on a related table using a lateral join. For example:

    select p.*, x.image_path
from posts p
left join lateral (
  select *
  from images i where i.post_id = p.id
  order by display_order
  limit 1
) x on true
    Login or Signup to reply.
  2. 0

    A classic PostgreSQL solution is a distinct on clause.

    select distinct on (post_id) post_id, image_path
from images 
order by post_id, display_order, image_path

    The first row for each post_id is defined with the order by

    The rest is a simple join

    with img as (
select distinct on (post_id) post_id, image_path
from images 
order by post_id, display_order, image_path
)
select p.id, p.title, img.image_path
from posts p
join img on p.id = img.post_id
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search