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

Select UserName From Position P Join Logon L
--- the join condition implies that UserID1 is not null
on P.UserID1 = L.UserID
union all
Select UserName From Position P Join on Logon L on P.UserID2 = L.UserID
where UserID1 is null

The case statement will work, but the isnull function is better.
select isnull(columnA, columnB).

If you have multiple columns to compare for nulls use coalesce.  select
coalesce(columnA,columnB,columnC....)

I tried to use the Case statement, but got an error on the "=" sign.

I don't see how the isnull or coalesce.helps me.  If I were looking for the
IsNull from the data that would be fine, but in my case I have 2 UserID,
which would be different records in the Logon table.  I need to use a Join
to get the correct UserName, I believe.  Using the isnull would get me the
correct UserID, but I still don't have the User Name for that UserID.

Perhaps 2 separate joins.

Thanks,

Tom

tshad (tscheider***@ftsolutions.com) writes:
CASE is not a statement in T-SQL, it is an expression. And just like
any other expression it returns a value. Thus what you had:

   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)

Does not cut it, because

1) ON appears to eaarly, it should come after the table with its alias.
2) The JOIN operator is followed by a boolean expression, but CASE
   can never return boolean, because there is no boolean datatype in
   SQL.
3) And therefore the value of one branch in the CASE cannot be
   "P.UserID1 = L.UserID".

This you can write:

   Select UserName
   From Position P
   Join Logon L  ON L.UserUD = CASE WHEN P.UserID1 is not null
                                    Then  P.UserID1
                                    ELSE  P.UserID2
                                END

coalesce(P.UserID1, P.UserID2) is a short-hand notation for the same
thing.

That makes sense now.

Show quoteHide quoteThat works great.

This also would work.  I misunderstood what Gary was saying.

Thanks,

Tom

Show quoteHide quote

tshad wrote:

Of course...

Show quoteHide quoteSelect UserName
From Position P Join Logon L
on coalesce(P.UserID1, P.UserID2) = L.UserID

Dieter

Using Case:

Select UserName From Position P Join Logon L On L.UserId = Case When
P.UserId1 Is Not Null Then P.UserId1 Else P.UserId2 End

Using Coalesce:

Select UserName From Position P Join Logon L On L.UserId =
Coalesce(P.UserId1, P.UserId2)

Using Isnull:

Select UserName From Position P Join Logon L On L.UserId =
Isnull(P.Userid1, P.UserId2)

tshad wrote:
Show quoteHide quote

Is performance a concern for you? Are the tables big?

Field value determines whether there is extra info that should be held about that record
sql statement formatting
Use of NULL and DEFAULT
Record locking within a stored procedure
compare '002' and '00²'