Giorgi Cercvadze Giorgi Cercvadze - 1 month ago 8
SQL Question

MySQL table alias does not work

So I am using 'as' command in this code

select PERSONAL_ID,NAME,SURNAME,BIRTH_DATE,NUMBER_OF_ORDERS
from(
select PERSONAL_ID,NAME,SURNAME,BIRTH_DATE,count(CUSTOMER_ID) as NUMBER_OF_ORDERS
from customer as C
right join
customer_hotel as CH on CH.CUSTOMER_ID = C.ID
group by(CH.CUSTOMER_ID)
) as O
where NUMBER_OF_ORDERS = (select max(NUMBER_OF_ORDERS) from O);


and it says: Table 'company.o' doesn't exist.
but when I do it like this

select PERSONAL_ID,NAME,SURNAME,BIRTH_DATE,NUMBER_OF_ORDERS
from(
select PERSONAL_ID,NAME,SURNAME,BIRTH_DATE,count(CUSTOMER_ID) as NUMBER_OF_ORDERS
from customer as C
right join
customer_hotel as CH on CH.CUSTOMER_ID = C.ID
group by(CH.CUSTOMER_ID)
) as O
where NUMBER_OF_ORDERS = (select max(NUMBER_OF_ORDERS)
from(
select PERSONAL_ID,NAME,SURNAME,BIRTH_DATE,count(CUSTOMER_ID) as NUMBER_OF_ORDERS
from customer as C
right join
customer_hotel as CH on CH.CUSTOMER_ID = C.ID
group by(CH.CUSTOMER_ID)
) as O);


it works properly. any suggestions?

Answer

Assigning an alias to a subquery doesn't mean it can be treated exactly like a table.

In the first query, mysql has no way to know that the O in select max(NUMBER_OF_ORDERS) from O isn't a table. So that's how it treats it, hence the error.

Is company is the name of your schema?

BTW, perhaps you could return the same information using:

select PERSONAL_ID,NAME,SURNAME,BIRTH_DATE,count(CUSTOMER_ID) as NUMBER_OF_ORDERS
from customer as C
right join
customer_hotel as CH on CH.CUSTOMER_ID = C.ID
group by(CH.CUSTOMER_ID)
order by count(CUSTOMER_ID) desc
limit 1;