Take non null value to do the inner join in sql -

i have code here:

select c.case_code,coalesce(c.case1, c.case2) case_def,aspm.userid cases c left join aspnet_membership aspm on c.case_def = aspm.userid 

and well, since there alias in on clause, won't recognize case_def.

what need is, need take non null value between case1 , case2 , need left join in query.

how do this? in advance.

select c.case_code,coalesce(c.case1, c.case2) case_def,aspm.userid cases c left join aspnet_membership aspm on coalesce(c.case1, c.case2) = aspm.userid 

you can't specify alias used in select clause elsewhere in query. however, dbms' accept that.