CODEWITHSUNDEEP
mysqlsubqueryalias
Simon Newton
Simon Newton

Reputation: 41

Joining the same subquery twice in MySQL

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?

Upvotes: 3

Views: 3232

Answers (1)

spencer7593
spencer7593

Reputation: 108410

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.

Upvotes: 7

Related Questions