[MariaDB] 'selects cannot be used in field list?' 해결방법

1. 변경전

select b.app_user_id,b.username,a.message_content,a.message_to,a.message_date 
from app_messages a 
left join app_users b 
on a.message_from = b.app_user_id
where a.message_to=1 and b.app_user_id= 4
UNION 
select b1.app_user_id,b1.username,a1.message_content,a1.message_to,a1.message_date 
from app_messages a1 
left join app_users b1 
on a1.message_from = b1.app_user_id 
where a1.message_to=4 and b.app_user_id= 1 
order by a1.message_date,a.message_date

 

2. 변경후 : select로 묶어서 해결

select * from
(
select b.app_user_id,b.username,a.message_content,a.message_to,a.message_date 
from app_messages a 
left join app_users b 
on a.message_from = b.app_user_id
where a.message_to=1 and b.app_user_id= 4
UNION 
select b1.app_user_id,b1.username,a1.message_content,a1.message_to,a1.message_date 
from app_messages a1 
left join app_users b1 
on a1.message_from = b1.app_user_id 
where a1.message_to=4 and b1.app_user_id= 1 
)A order by message_date

 

출처 : https://stackoverflow.com/questions/53809027/how-to-fix-mysql-error-number-1250-table-a1-from-one-of-the-selects-cannot-be