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

How to restrict the result of a left join and use it for the next left join? – Postgresql

My query goes over three entities, so I have two left joins. I want to restrict the result of the first join. For example we have customers and order evaluations. In the first join I want only the youngest order evaluation that is hanging onto the customer. The older ones don’t interest me. After this I join over another table (with dependency to customer and orderevaluation) and ask for certain conditions. I can’t seem to find out how I can restrict the first join result with the help of the createstamp.

My query draft looks like this:

SELECT * FROM customer 
left join (SELECT * FROM orderevaluation ORDER BY createstamp desc LIMIT 1) o on customer.id = o.customer_id
left join ... WHERE ... AND ... ;

Here is the problem that there gets only one orderevaluation selected for all my orders and not one per order.
I want to select the youngest orderevaluation per customer and then join again.

I also tried:

Selecting after the

customer.id =

But there I could only work with the id and not with the createstamp.

I tried to use ORDER BY o.createstamp DESC limit 1 after the WHERE condition but it doesn’t work, either.

Tags:

2

Answers


  1. 0

    I want to select the youngest orderevaluation per customer and then join again.

    You would typically filter the first table in a subquery. In Postgres, one approach uses distinct on. Starting from your pseudo-code:

    SELECT ...  -- enumerate wanted cols here 
FROM customer c
LEFT JOIN (
    SELECT DISTINCT ON (customer_id) o.*
    FROM orderevaluation o
    ORDER BY customer_id, createstamp DESC
) o on customer.id = o.customer_id
LEFR JOIN ... 
WHERE ...

    Another approach would be to use lateral joins instead of left joins (and then we could indeed use limit, as in your attempt) – which would require more context about the design of your tables.

    Login or Signup to reply.
  2. 0

    Typically, you run such a query for one or a few selected customers (filtered in the undisclosed outer WHERE clause). Then, a LATERAL subquery is typically fastest, as it only processes the few customers of interest instead of the whole table.

    SELECT ...
FROM   customer c
LEFT   JOIN LATERAL (
   SELECT o.*
   FROM   orderevaluation o
   WHERE  o.customer_id = c.id
   ORDER  BY o.customer_id, o.createstamp DESC NULLS LAST
   LIMIT  1
   ) o ON true
LEFT  JOIN ... 
WHERE ...  -- filtering *few* customers

    See:

    About the LATERAL join:

    Be sure to have an index with leading customer_id, ideally on orderevaluation(customer_id, customer_id, createstamp DESC NULLS LAST).

    Drop NULLS LAST from index and query if the column is defined NOT NULL.

    For additional joins, consider:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search