0

I have this code here:

SELECT c.Case_Code,COALESCE(c.case1, c.case2) AS case_def,aspm.UserId
FROM Cases AS c
LEFT JOIN aspnet_Membership AS aspm
ON c.case_def = aspm.userId

And well, since there is an Alias in the On clause, it won't recognize the case_def.

What i need to do is, i need to take the non null value between case1 and case2 and i need to Left Join it as i do in my query.

How do i do this? Thanks in advance.

Oz Magician
  • 53
  • 1
  • 8

1 Answers1

3
SELECT c.Case_Code,COALESCE(c.case1, c.case2) AS case_def,aspm.UserId
FROM Cases AS c
LEFT JOIN aspnet_Membership AS aspm
ON COALESCE(c.case1, c.case2) = aspm.userId

You can't specify an alias used in the select clause elsewhere in the query. However, some dbms' do accept that.

Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
  • 1
    Explanation as to why the alias can't be used on the inner join would be beneficial. Perhaps something like the order of processing for a select statement like that found [here](http://stackoverflow.com/questions/17403935/what-is-the-order-of-execution-for-this-sql-statement). This indicates that at time of execution, the FROM doesn't know about the aliased column yet. Thus you can't use the alias – xQbert Jul 23 '15 at 12:41