Use previous query result in a query

oracle

I'm building a football database. I store data about the position, points, etc of different teams in the past years.

I'm trying to write a query which gets all the teams which have ascended and then descended again. In order to do that, I've the following query.

SELECT DISTINCT team
    FROM Result
    WHERE number=42 AND 
        situation='ASCENT'
INTERSECT SELECT DISTINCT team
    FROM Result
    WHERE number=38 
        AND situation='DESCENT' 
        AND league='Premier league';

The key is that I need to express "temporality"; the year of the ascend has to be previous than the the year of the descend. In my Result table I've a year field, which I could use in order to compare the temporal order, but I don't know how to do it.

Best Answer

You can join the Result table to itself like this:

SELECT
  team
FROM
  Result A
  JOIN Result D ON D.team = A.team AND A.year < D.year
WHERE
  A.number=42
  AND
  A.situation='ASCENT'
  AND
  D.number=38
  AND
  D.situation='DESCENT'
  AND
  D.league='Premier league'
Related Question