-2

Could someone explain to me, why this will work:

select t1.id, t1.age 
from mytable t1 
join (select age 
      from mytable 
      group by age) t2 
  on t1.age = t2.age

while using t1 in the second part of the join statement like so 

select t1.id, t1.age 
from mytable t1 
join (select age 
      from t1 
      group by age) t2 
  on t1.age=t2.age

gives me an error?

ERROR 1146 (42S02) at line 17: Table '[...].t1' doesn't exist

In my actual problem, mytable corresponds to a selection, so I would like to use t1 in order avoid having to copy-paste the whole select ... from etc statement into the second part of the join statement.

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
Johann
  • 572
  • 6
  • 22

2 Answers2

2

You can not reference in a subquery an outer query, because the parser starts evaluating the subqueries, i.e., in your case 

(select age from t1 group by age)

As table t1 does not exist, it will not work. Since MySQL does not implement yet Commom Table Expressions (CTE) (it will in Mysql version 8.0) if you really are not willing to repeat the code, you must use Derived Tables or Temporary Tables or views. See this other post

I think the best choice is just to repeat the code.

dba_cabuloso
  • 21
  • 4
0

You cannot use an alias in the FROM clause.

isaace
  • 3,336
  • 1
  • 9
  • 22