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

Question

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 great for this.

Joe Stefanelli · Accepted Answer · 2011-07-25 18:57:23Z

6

...COALESCE(Table1.Name, Table2.Name) AS RealName...

answered Jul 25, 2011 at 18:57

Joe Stefanelli

136k21 gold badges244 silver badges242 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joe Stefanelli Over a year ago

@ain: Sure it does. http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce

Richard Marskell - Drackir · Accepted Answer · 2011-07-25 19:02:17Z

4

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.

edited Jul 25, 2011 at 19:02

answered Jul 25, 2011 at 18:54

Richard Marskell - Drackir

13.4k3 gold badges37 silver badges55 bronze badges

Comments

Rahul · Accepted Answer · 2011-07-25 18:57:14Z

0

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

answered Jul 25, 2011 at 18:57

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Comments

Adriano Carneiro · Accepted Answer · 2011-07-25 19:19:46Z

0

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.

edited Jul 25, 2011 at 19:19

Adriano Carneiro

58.8k12 gold badges94 silver badges123 bronze badges

answered Jul 25, 2011 at 18:58

Frank Schmitt

30.5k13 gold badges79 silver badges111 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related