开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜