select - Getting unknown column error when using 'as' in mysql statement -

i have mysql statement:

select a.id, a.`from member_id`, a.`to member_id`, if(a.`from member_id`=1, a.`to member_id`, a.`from member_id`) other_id, a.text, max(a.`date sent`) `date sent` message join members m on other_id=m.id (a.`from member_id`=1 or a.`to member_id`=1) , a.active=1 group other_id order other_id desc, `date sent` desc 

but getting error:

#1054 - unknown column 'other_id' in 'on clause'  

i creating column using as key. know whats wrong here?

thanks.

the as creates column alias (in case other_id), , can't join on column alias. can use alias in order by else, unless alias comes subquery.

your best option here repeat if function in join:

select   a.id,   a.from member_id,   a.to member_id,   if(a.from member_id=1, a.to member_id, a.from member_id) other_id,   a.text,   max(a.date sent) date sent message join members m on if(a.from member_id=1, a.to member_id, a.from member_id) = m.id (a.from member_id=1 or a.to member_id=1) , a.active=1 group other_id order other_id desc, date sent desc