MySQL: How to create an alias from two different fields?
I have a query that is joining two tables. I would like to be able to create an alias that allows the value to default to a different field when the original field is null. Here is an example:
select
Table1.Name,
Table2.Name,
(case when Table1.Name then Table2.Name else Table1.Name end) AS 'RealName'
from Table3
left join Table1 on Table1.ID = Table3.Table1_ID
left join Table2 on Table2.ID = Table3.Table2_ID
order by `RealName` asc
I am getting an "unknown column in field list" error when I try doing this.
Update: Turns out my unknown column error was due to something else. The MySQL coalesce function worked gre开发者_如何学Cat for this.
...COALESCE(Table1.Name, Table2.Name) AS RealName...
Try this instead:
(case when Table1.Name IS NULL then Table2.Name else Table1.Name end) AS 'RealName'
Edit
And also in your Order by, change it to:
order by (case when Table1.Name IS NULL then Table2.Name else Table1.Name end) asc
Edit 2
As others have stated, you can also use coalesce(Table1.Name, Table2.Name)
but I believe the error is still because you're referencing the aliased column in the order by
. With either method, just put the code you're aliasing in the order by
and it should be fine.
Try this once
select
Table1.Name,
Table2.Name,
(case when Table1.Name is null then Table2.Name else Table1.Name end) AS 'RealName' from Table3 left join Table1 on Table1.ID = Table3.Table1_ID left join Table2 on Table2.ID = Table3.Table2_ID order by `RealName` asc
You could also use COALESCE
:
Select * from (select
Table1.Name,
Table2.Name,
coalesce(Table1.Name, Table2.Name) AS RealName
from Table3
left join Table1 on Table1.ID = Table3.Table1_ID
left join Table2 on Table2.ID = Table3.Table2_ID)
order by RealName asc
I don't know about MySQL, but other RDBMS won't let you use the RealName alias inside the "inner" query, therefore I've added the "outer" query.
精彩评论