MySQL table alias does not work

Question

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?

Sorry, but I think this whole thing is a bit of a mess (including the bit that you think 'works properly'). See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Nov 04 '16 at 16:36
@CGritton not really, it works only if only one row exists with maximum value, but if there are 3 rows with maximum it will only return one. — Giorgi Cercvadze, Nov 04 '16 at 16:57
@GiorgiCercvadze, ah, gotcha. What you are doing makes sense then. — CLAbeel, Nov 04 '16 at 17:10

CLAbeel · Accepted Answer · 2016-11-04T16:48:09.977

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;

MySQL table alias does not work

1 Answers1