CODEWITHSUNDEEP
sqlpostgresqlsubquery
Obvious_Grapefruit
Obvious_Grapefruit

Reputation: 870

How to use column derived from SELECT subquery elsewhere in SQL query

I have two database tables that look something like this:

+----------+      +------------------+
| Bookings |----->| Booking_Statuses |
+----------+      +------------------+

Context:

I am trying to query the bookings for a particular user, along with its latest status:

SELECT "bookings".*,
       (
            SELECT "status"
            FROM "booking_statuses"
            WHERE "bookings"."id" = "booking_statuses"."booking_id"
            ORDER BY "created_at" DESC
            LIMIT 1
       ) AS "last_status"
FROM "bookings"
WHERE "user_id" = $1
AND "last_status" IN ('PENDING', 'APPROVED')

It all works great, until I add the last line - AND "last_status" IN ('PENDING', 'APPROVED'), at which point I get the error: column "last_status" does not exist.

How can I use last_status outside of the SELECT subquery?

Upvotes: 1

Views: 521

Answers (2)

upizs
upizs

Reputation: 89

Can you use something like this? You find your latest status id and then join it to bookings and then you can get the fields you need.

SELECT B.*, BB."status"
FROM "bookings" B
JOIN "booking_statuses" BB ON BB.id = (SELECT id
            FROM "booking_statuses"
            WHERE B."id" = "booking_statuses"."booking_id"
            ORDER BY "created_at" DESC
            LIMIT 1)
WHERE "user_id" = $1
AND BB."status" IN ('PENDING', 'APPROVED')

Upvotes: 1

WriterState
WriterState

Reputation: 369

Try something like this

SELECT bookings.*,
       status as last_status
FROM   bookings
       INNER JOIN booking_statuses
               ON id = booking_id
WHERE  user_id = $1
       AND last_status IN ( 'PENDING', 'APPROVED' )
ORDER  BY created_at DESC
LIMIT  1

Upvotes: 1

Related Questions