3

I have two subqueries that result in a list of fixtures, and a list of teams that the user has admin privileges for.

I can doing this having to call the same (quite complicated) subquery twice for the list of teams, like this:


(SELECT hometeam, awayteam etc... ) as fixtures

LEFT JOIN (SELECT team_id, admin etc... ) as teams1 ON fixtures.hometeam = teams1.team_id
LEFT JOIN (SELECT team_id, admin etc... ) as teams2 ON fixtures.awayteam = teams2.team_id

Is there a way of doing it using an alias for the team list query without having to execute it twice?

Simon Newton
  • 41
  • 1
  • 2
  • Possible duplicate of [Reuse mysql Subquery in InnerJoin](https://stackoverflow.com/questions/19825615/reuse-mysql-subquery-in-innerjoin) – robsiemb Oct 01 '19 at 20:02
  • As Spencer's answer below says, you can only truly do this in the more recent versions of MySQL, where they have add support for CTEs. Before this version, the only real option is to create a table (not a true TEMPORARY one though), populate it with the subquery's results, and use that table in place of the subquery, then drop the intermediate table when you're done... and you'll need to be careful that multiple clients are not trying to do this (with the same intermediate table name at least) at the same time. – Uueerdo Oct 01 '19 at 22:33

1 Answers1

7

MySQL 8.0 introduces support for Common Table Expressions (CTE)

Note that this is not supported in earlier versions of MySQL i.e. not available in 5.7, 5.6,

https://dev.mysql.com/doc/refman/8.0/en/with.html

something like this:

WITH
  teams AS (SELECT team_id, admin etc... )
SELECT ...
  FROM (SELECT hometeam, awayteam etc... ) AS fixtures
  LEFT
  JOIN teams t1 
    ON t1.team_id = fixtures.hometeam
  LEFT 
  JOIN teams t2
    ON t2.team_id = fixtures.awayteam
 WHERE ...

For versions of MySQL before 8.0, versions that don't support CTE, there's not a way to reference the same inline view multiple times.

spencer7593
  • 106,611
  • 15
  • 112
  • 140