CODEWITHSUNDEEP
sqlmysql
Kamran Ahmed
Kamran Ahmed

Reputation: 12440

MySQL alias and Table name

I tried to execute the following query:

SELECT * FROM `pd_cheque` as p WHERE `pd_cheque`.etype='pd_issue'

And it gave me the following error:

#1054 - Unknown column 'pd_cheque.etype' in 'where clause'

After "some" experiments, I got the following query to work:

SELECT * FROM `pd_cheque` as p WHERE p.etype='pd_issue'

Why is that MySQL didn't let me execute the first query? Can a table name, when once assigned an alias, cannot be reference again with the table name?

Upvotes: 4

Views: 2836

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

In the first query:

SELECT *
FROM `pd_cheque` as p
WHERE `pd_cheque`.etype='pd_issue'

You have renamed the table using a table alias. Everywhere else in the query, you need to use p to refer to the table. The query no longer recognizes the original table name, because it has been renamed.

Table aliases are required for self-joins. Consider:

select
from t join
     t
     on t.id = t.otherid

It just doesn't make sense without aliases:

select
from t join
     t tother
     on tother.id = t.otherid;

In addition, table aliases make queries easier to read.

Upvotes: 5

juergen d
juergen d

Reputation: 204924

Because when you define an alias for a table, as you did

`pd_cheque` as p

then you have to use it instead of the original table name.

Upvotes: 1

Related Questions