Case in inner join

Hi All,
Is it possible to use Case statement inside Joins?
Say,
Select ........ from TableA A
inner join TableB B on
Case
When @.filter <> ''
Then B.ID = A.ID
and B.ID in (Select id from temptable where Name = @.filter)
Else B.ID = A.ID
End
inner join TableC C on C.ID = A.ID
where....................................
Thanks in advance...
KuttyTry this
Run this on Pubs, change the 1 = 1 to false to test the fall through option
where you just join on A.id = b.id.
select * from titles a
inner join titleauthor b
on a.[title_id] = case when (1 = 1) then ('PS3333') else b.title_id end
and a.title_id = case when (1 = 1) then (b.title_id) end
This is how your code can be changed.
select * from TableA a
inner join TableB b
on B.ID = case when (@.filter <> '') then (@.filter) else (A.ID) end
and B.ID = case when (@.filter <> '') then (A.ID) end
Don't know if this will work.
Hope it's what you are looking for.
"Pradeep Kutty" wrote:

> Hi All,
> Is it possible to use Case statement inside Joins?
> Say,
> Select ........ from TableA A
> inner join TableB B on
> Case
> When @.filter <> ''
> Then B.ID = A.ID
> and B.ID in (Select id from temptable where Name = @.filter
)
> Else B.ID = A.ID
> End
> inner join TableC C on C.ID = A.ID
> where....................................
>
> Thanks in advance...
> Kutty
>
>|||Hi
Is it still open ? Did not you resolve the problem?
Use pubs
SELECT a.au_lname, a.au_fname, a.address,
t.title, t.type
FROM authors a INNER JOIN
titleauthor ta ON ta.au_id = a.au_id INNER JOIN
titles t ON t.title_id = ta.title_id
INNER JOIN publishers p on t.pub_id =
CASE WHEN t.type = 'Business' THEN p.pub_id ELSE null END
INNER JOIN stores s on s.stor_id =
CASE WHEN t.type = 'Popular_comp' THEN t.title_id ELSE null END
"Pradeep Kutty" <pradeepk@.healthasyst.com> wrote in message
news:%23Hq1W3XSFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> Is it possible to use Case statement inside Joins?
> Say,
> Select ........ from TableA A
> inner join TableB B on
> Case
> When @.filter <> ''
> Then B.ID = A.ID
> and B.ID in (Select id from temptable where Name =
@.filter)
> Else B.ID = A.ID
> End
> inner join TableC C on C.ID = A.ID
> where....................................
>
> Thanks in advance...
> Kutty
>|||There is no CASE statement in SQL. There is a CASE **expression**.
Expressions return values. Since SQL is a declarative language, your
guess at the syntax is fundamentally wrong; you still think you are
writing procedural code.|||--CELKO-- wrote:
> There is no CASE statement in SQL. There is a CASE **expression**.
> Expressions return values. Since SQL is a declarative language, your
> guess at the syntax is fundamentally wrong; you still think you are
> writing procedural code.
And, Joe, you still think yer Codd's gift to earth.
Lighten up, crotchety! Get laid, or somethin.