Case in Join

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,
TomSelect 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...)|||"Gary Gibbs" <ggibbs@.aahs.org> wrote in message
news:1135974528.225930.160070@.g44g2000cwa.googlegroups.com...
> The case statement will work, but the isnull function is better.
> select isnull(columnA, columnB).
I tried to use the Case statement, but got an error on the "=" sign.

> If you have multiple columns to compare for nulls use coalesce. select
> coalesce(columnA,columnB,columnC...)
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 wrote:

> Can you put a Case statement in a Join?
Of course...

> 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)
Select 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:
> 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
>|||Is performance a concern for you? Are the tables big?|||tshad (tscheiderich@.ftsolutions.com) writes:
> "Gary Gibbs" <ggibbs@.aahs.org> wrote in message
> news:1135974528.225930.160070@.g44g2000cwa.googlegroups.com...
> I tried to use the Case statement, but got an error on the "=" sign.
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.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns973CF3D7CD071Yazorman@.127.0.0.1...
> tshad (tscheiderich@.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:
That makes sense now.

> 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
That works great.

> coalesce(P.UserID1, P.UserID2) is a short-hand notation for the same
> thing.
This also would work. I misunderstood what Gary was saying.
Thanks,
Tom

>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx