CASE statement within join

Is it possible to have a join with case statement in it?
i.e.
select * from a inner join b on
case [x] then a.xid = b.xid
case [y] then a.yid = b.yid?What condition are you checking for?

That's a pretty malformed CASE statement you've got there...|||Wanted to do a join based on case statements. Instead I will just use the case statements in a function and join based on function.

This is probably not clear, but basically to join based on case statements (i.e. join two tables based on CASE of a field) use:

1. left outer join for each CASE, thus not using CASE in syntax, but using left outer join in place of CASE

2. create a function that checks the field in CASE and returns value. Then join based on function i.e. dbo.wholeword(a.searchword) = b.searchword|||BUT WHAT CONDITION DO YOU WANT TO CHECK FOR?

Is this not a clear request?

My best guess is that you want 2 queries and a union

Good luck

case join statement

I have parameter @.cona.
If @.cona=1 then
............
from lokStanje l INNER JOIN
(objSta Sc INNER JOIN artCona Ac ON Sc.ART_SIF=Ac.ART_SIF AND
Sc.STA_OZN=@.STA_OZN INNER JOIN LOKCONA Lc ON Ac.CONA=Lc.CONA)
ON l.LOK_OZN=Lc.LOK_OZN
LEFT JOIN .............
If @.cona=0 then--I don't need INNER JOIN:
............
from lokStanje l
LEFT JOIN .............
I don't won't to write 2 queries, one for @.cona=0 and one for @.cona=1
because query is quite large.
I would like to know if it's possible to somehow combine @.cona in my query,
something like:
............
from lokStanje l case when @.cona=1 then INNER JOIN
(objSta Sc INNER JOIN artCona Ac ON Sc.ART_SIF=Ac.ART_SIF AND
Sc.STA_OZN=@.STA_OZN INNER JOIN LOKCONA Lc ON Ac.CONA=Lc.CONA)
ON l.LOK_OZN=Lc.LOK_OZN end
LEFT JOIN .............
Thank you for your answer,
SimonWith the least code possible:
............
from lokStanje l INNER JOIN
(objSta Sc INNER JOIN artCona Ac ON Sc.ART_SIF=Ac.ART_SIF AND
Sc.STA_OZN=@.STA_OZN INNER JOIN LOKCONA Lc ON Ac.CONA=Lc.CONA)
ON l.LOK_OZN=Lc.LOK_OZN
OR @.cona=0
LEFT JOIN .............
However, this might not result in the optimal query plan. If you are coding
to get the best performance, having two separate statements (maybe even in 2
separate stored procedures) is the better solution, as the best query plan
will be used in every case.
Jacco Schalkwijk
SQL Server MVP
"simon" <simon.zupan@.stud-moderna.si> wrote in message
news:uTzxdOCPFHA.3336@.TK2MSFTNGP09.phx.gbl...
>I have parameter @.cona.
> If @.cona=1 then
> ............
> from lokStanje l INNER JOIN
> (objSta Sc INNER JOIN artCona Ac ON Sc.ART_SIF=Ac.ART_SIF AND
> Sc.STA_OZN=@.STA_OZN INNER JOIN LOKCONA Lc ON Ac.CONA=Lc.CONA)
> ON l.LOK_OZN=Lc.LOK_OZN
> LEFT JOIN .............
>
> If @.cona=0 then--I don't need INNER JOIN:
> ............
> from lokStanje l
> LEFT JOIN .............
> I don't won't to write 2 queries, one for @.cona=0 and one for @.cona=1
> because query is quite large.
> I would like to know if it's possible to somehow combine @.cona in my
> query, something like:
> ............
> from lokStanje l case when @.cona=1 then INNER JOIN
> (objSta Sc INNER JOIN artCona Ac ON Sc.ART_SIF=Ac.ART_SIF AND
> Sc.STA_OZN=@.STA_OZN INNER JOIN LOKCONA Lc ON Ac.CONA=Lc.CONA)
> ON l.LOK_OZN=Lc.LOK_OZN end
> LEFT JOIN .............
> Thank you for your answer,
> Simon
>

Case in Inner Join? SQL statement help!!

Hi,

I have a 2 different telephone number tables,

Table 1 has some numbers beginning with '0' i.e. 08001234567 and some without the '0' i.e 8001234567

on my Table2 i only have numbers starting with '0' i.e 08001234567.

I would like to make a INNER JOIN statement and check if the telephone number dont have a starting '0', then append to it and try to do the join so I get both set of data.

for example (this doesnt work however...):

SELECT *

FROM dbo.Calls INNER JOIN
ON

CASE WHEN SUBSTRING(dbo.CallData.TelephoneNumber, 0, 1) = '0'

THEN dbo.Calls.TelephoneNumber = dbo.Post.TelephoneNumber

ELSE '0' & dbo.Calls.TelephoneNumber = dbo.Post.TelephoneNumber <--append a 0 at the start

END CASE

AND dbo.Products.FK_Client = dbo.CallDataSets.FK_Client
GROUP BY dbo.CallDataSets.FK_Client

I hope you understand what I am trying to achieve here...

Any help is appreciated!

Many thanks,

Jon

Try something like this:

Code Snippet

SELECT *
FROM dbo.Calls c
JOIN ...
ON ...
JOIN Post p
ON ( right(( '0' + c.TelephoneNumber ), 11 )) = ( right(( '0' + p.TelephoneNumber ), 11 ))
JOIN Products pr
ON ...

You only need to add the leading zero on the table that requires it -both are used above for illustration.

|||

thanks for the reply.

but the reason i need the CASE STATEMENT is that some of them don't have Zeros, and it wont join the table.

I want both numbers to be join with.

many thanks.

|||

So, did you even try the suggestion I sent.

If you did, what happened?

I have a 2 different telephone number tables,

Table 1 has some numbers beginning with '0' i.e. 08001234567 and some without the '0' i.e 8001234567

on my Table2 i only have numbers starting with '0' i.e 08001234567.

And of course, you could choose to correct the data that doesn't have a leading zero with an UPDATE statement like this (then both tables would have data in the same form):

Code Snippet

UPDATE Table1
SET TelephoneNumber = ( '0' + TelephoneNumber )
WHERE len( TelephoneNumber ) = 10

(or)

...
WHERE left( TelephoneNumber, 1 ) <> '0'

|||

thanks!

in the end is use CAST(object as bigint) to get rid or the zero.

|||That would also work just as well.

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.