Use previous query result in a query
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 &lt; D.year
WHERE
  A.number=42
  AND
  A.situation='ASCENT'
  AND
  D.number=38
  AND
  D.situation='DESCENT'
  AND
  D.league='Premier league'