CODEWITHSUNDEEP
sql
Artur Korobeynyk
Artur Korobeynyk

Reputation: 987

Fetching records from database which have a similar field(s) but different dates?

I need help with a query to for my table 'ITEM' with fields: ID, NAME, STATUS, RECEIVED_AT (TIMESTAMP).

I need to get all records where status = 'canceled', but only if there is another record in this table with status = 'available' that has an earlier received_at date. I am not experienced enough to create the correct query, but would like for someone to help me with the correct query or direct me to a site that explains basic SQL queries so that I can learn.

Upvotes: 0

Views: 66

Answers (1)

Cade Roux
Cade Roux

Reputation: 89671

Naively (i.e. literally and without optimization - note that the predicate is correlated with the current version of the ITEM table):

SELECT *
FROM ITEM AS current
WHERE current.status = 'canceled'
    AND EXISTS (
        SELECT *
        FROM ITEM AS earlier
        WHERE earlier.status  = 'available'
            AND earlier.RECEIVED_AT < current.RECEIVED_AT
    )

Upvotes: 5

Related Questions