Case in Join

[tshad]

Can you put a Case statement in a Join?

My problem is that I have 2 possible fields I want to join to the same
table. If one is null, use the other.

For example:

Create Table Position
(
UserID1 int,
UserID2 int
)

Create Table Logon
(
UserID int,
UserName
)

I want to do something like (I know this doesn't work, but you should get
the idea of what I am looking for from it).

Select UserName From Position P Join on Logon L (CASE WHEN UserID1 is not
null Then (P.UserID1 = L.UserID) ELSE (P.UserID2 = L.UserID) END)

I am trying to get the User Name from UserID1, unless it is null. If that
is the case, then get the User Name from UserID2.

Thanks,

Tom

 

[Guest]

Check out the coalesce() in SQL

Your SQL would be something like "on coalesce(p.UserID1,p.UserID2) = L.UserID"

 

[sydney.luu]

Try this and assume Logon.UserName is VARCHAR:

select l.username
from logon l
join (select isnull(userid1,userid2) userid
from position) vt
on l.userid = vt.userid

-Sydney

 

[sydney.luu]

Try this and assume Logon.UserName is VARCHAR:

select l.username
from logon l
join (select isnull(userid1,userid2) userid
from position) vt
on l.userid = vt.userid

-Sydney