Avatar of lbarnett419
lbarnett419 asked on

Case Statement in Where Clause

I have 2 tables where I need to select records if the identifiers can be joined. There are 2 sets of identifiers...I want to compare the first ones and if they join, then select the record. If they don't match, try to join on the second identifier. If that doesn't work, then the record isn't selected.

Do I use a case statement in the where clause?

Select a.stateid1, b.stateid2, a.ssn1,b.ssn2, a.lastname,a.firstname
from tablex a, tabley b
where
case a.stateid1= b.stateid2
then end?
else a.ssn1=b.ssn2
end
Microsoft SQL Server

Avatar of undefined
Last Comment
lbarnett419

8/22/2022 - Mon
jazarfinn

You are allowed to use case statements in where clauses but the following is much easier:

Select a.stateid1, b.stateid2, a.ssn1,b.ssn2, a.lastname,a.firstname
from tablex a join tabley b
on (a.stateid1= b.stateid2) or (a.ssn1=b.ssn2)
ASKER
lbarnett419

I wasn't thorough in my question:
Each table can have blank or null ssns or stateids in the record so I could get cartesian joins using either join.
 I created a new table out of one of the tables which populated a field called 'Identifier' with either the SSN or the StateId if either was blank.
Can I use the 'Identifier' field to compare against the ssn and if that doesn't match, the stateid?  I know I need to add more joins if there are blanks, but I still need to compare those fields.
ex:
Select a.stateid1, b.stateid2, a.ssn1,b.ssn2, a.lastname,a.firstname
from tablex a join tabley b
where (a.identifier = b.ssn2 ) or (a.identifier=b.stateid2)                      ????
chapmandew

try something like this:

Select a.stateid1, b.stateid2, a.ssn1,b.ssn2, a.lastname,a.firstname
from tablex a, tabley b
where
case when a.stateid1= b.stateid2 = then 1 else 0 end = 1 OR
case when a.ssn1=b.ssn2 then 1 else 0 end = 1
end
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Lexie

Yes, you can use this query:

Select a.stateid1, b.stateid2, a.ssn1,b.ssn2, a.lastname,a.firstname
from tablex a join tabley b
where (a.identifier = b.ssn2 ) or (a.identifier=b.stateid2)  

However, if an identifier, let us say identifier=400, is used of both a ssn2 or stateid2 you will get two rows for one identifier. You can prevent this like this (giving ssn2  the preference):

Select a.stateid1, isnull(b1.stateid2, b2.stateid2) as stateid2, a.ssn1,isnull(b1.ssn2, b2.ssn2) as ssn2 a.lastname,a.firstname
from tablex a
left join tabley b1 on a.identifier = b1.stateid2
left join tabley b2 on a.identifier = b2.ssn2 and b1.stateid2 IS NULL
where isnull(b1.stateid2, b2.ssn2) IS NOT NULL
Lexie

Oh, I gave stateid2 the preference, it should be like this giving ssn2  the preference:

Select a.stateid1, isnull(b1.stateid2, b2.stateid2) as stateid2, a.ssn1,isnull(b1.ssn2, b2.ssn2) as ssn2 a.lastname,a.firstname
from tablex a
left join tabley b1 on a.identifier = b1.ssn2  
left join tabley b2 on a.identifier = b2.stateid2 and b1.ssn2  IS NULL
where isnull(b1.stateid2, b2.ssn2) IS NOT NULL
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Lexie

I just had a small performance test, my query turns out to be faster. The second needs some more tuning:

CREATE TABLE A (identifier INTEGER)
CREATE TABLE B (ssn2 INTEGER, stateid2 INTEGER)

CREATE INDEX i_index1 ON A (identifier)
CREATE INDEX i_index2 ON B (ssn2)
CREATE INDEX i_index3 ON B (stateid2)

DECLARE @intCounter AS INTEGER
SET @intCounter = 1
WHILE @intCounter < 10000
BEGIN
   INSERT INTO A (identifier) VALUES (@intCounter)
   IF @intCounter%3 = 0 INSERT INTO B (ssn2) VALUES (@intCounter)
   IF @intCounter%7 = 0 INSERT INTO B (stateid2) VALUES (@intCounter)  
   SET @intCounter= @intCounter + 1
END

PRINT CONVERT(VARCHAR, GETDATE(), 121)

-- Query 1
SELECT a.identifier, b1.*, b2.*
FROM a
LEFT JOIN b b1 ON a.identifier = b1.ssn2  
LEFT JOIN b b2 ON a.identifier = b2.stateid2 AND b1.ssn2 IS NULL
WHERE ISNULL(b1.ssn2, b2.stateid2) IS NOT NULL

PRINT CONVERT(VARCHAR, GETDATE(), 121)

-- Query 2
SELECT a.identifier, b.*
FROM a JOIN b ON
    (a.identifier = b.ssn2) OR
    (a.identifier = b.stateid2 AND NOT EXISTS(
        SELECT 1 FROM b WHERE a.identifier = b.ssn2))

PRINT CONVERT(VARCHAR, GETDATE(), 121)

The results:
Query 1 = 4285 records  0.3 seconds  
Query 2 = 4285 records  34 seconds  

Query 1
Table 'Worktable'. Scan count 0, logical reads 0.
Table 'A'. Scan count 1, logical reads 17
Table 'B'. Scan count 2, logical reads 22

Query 2
Table 'B'. Scan count 1431, logical reads 2871
Table 'Worktable'. Scan count 2, logical reads 168839
Table 'A'. Scan count 2, logical reads 34
Table 'Worktable'. Scan count 0, logical reads 0
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
lbarnett419

The combination of answers helped make the result successful. Thanks!!!