Geek Answers Handbook

MySQL: How to create an alias from two different fields?

I have a query joining two tables. I would like to be able to create an alias that allows the default value to use another 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 get an “unknown column in a field list” error when trying to do this.

Update: . It turns out my unknown column error was caused by something else. MySQL's collaboration feature did a great job of this.

+5
source share
4 answers
...COALESCE(Table1.Name, Table2.Name) AS RealName...
+6
source

Try this instead:

(case when Table1.Name IS NULL then Table2.Name else Table1.Name end) AS 'RealName'

Edit
And also in your Order, change it to:

order by (case when Table1.Name IS NULL then Table2.Name else Table1.Name end) asc

2
, coalesce(Table1.Name, Table2.Name), , - , order by. , order by, .

+4

Try 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
0
source

You can 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 do not know about MySQL, but other RDBMS will not allow you to use the RealName alias inside an internal query, so I added an "external" query.

0
source

More articles:


All Articles