Alias name cannot match another table/alias name in FROM Clause

Database
Enthusiast

Alias name cannot match another table/alias name in FROM Clause

Hello all,
Actually I'm trying to perform the following request and I receive this error :

Error 3870: Alias name cannot match another table/alias name in FROM clause.

select *

from table1 AS A inner join table2 AS B
ON A.field1=B.field1,
A inner join table3 AS C
ON A.field2=C.field2,
A inner join table4 AS D
ON A.field1=D.field1,
A inner join table5 AS E
ON A.field1=E.field1

where
C.DATE1 BETWEEN A.DATE2 and A.DATE2
;

As far as I can see it, each of my alias does correspond to one single table!
So why this error ?
Is it forbidden to join the same field of table 1 two times on different tables ?

Thank you very much if you find an answer.
BR
2 REPLIES
Senior Apprentice

Re: Alias name cannot match another table/alias name in FROM Clause

You should be glad about that error message ;-)
Your query is an 8-table mixing SQL89 and SQL92 style joins, it would result in cross-joining the result sets of those inner joins.

You probably wanted:
from table1 AS A
, table2 AS B
, table3 AS C
, table4 AS D
, table5 AS E
WHERE A.field1=B.field1
AND A.field2=C.field2
AND A.field1=D.field1
AND A.field1=E.field1

Rewritten using JOIN syntax:
from table1 AS A inner join table2 AS B
ON A.field1=B.field1
inner join table3 AS C
ON A.field2=C.field2
inner join table4 AS D
ON A.field1=D.field1
inner join table5 AS E
ON A.field1=E.field1

Dieter
Enthusiast

Re: Alias name cannot match another table/alias name in FROM Clause

Okay thank you very much, it works very fine !