Conditional Join Statements based on Variable - SQL Server

Question

I have a question about altering the where statements in the where clause based on a variable flag passed in by the UI.

In the scenario there are person records in the DB and the goal is to allow the user to find duplicates based on the criteria they select. It could be any permutation of the search options to be considered duplicates.

The query is simplified for the example, but the concept is the same. Would this be able to be included conditionally in the join using IF or CASE, or would each permutation need to be its own separate query?

Declare @lastnamedup varchar(1) = 'N',
        @firstnamedup varchar(1) = 'N',
        @DOBdup varchar(1) = 'N',
        @SSNdup = 'N',
        @ZIPdup= 'N'

/*UI values passed could set any to Y*/

select
   p.id
   p.lastname,
   p.firstname,
   p.SSN,
   p.DOB,
   p.Address1,
   p.Address2,
   p.zip
   p.phone
from person p
join person p2 on p.id <> p2id
               and /* conditionally join based on Dup flags activated
               could be 1 or many of any of the 5 dup variables for example
               @lastnamedup = 'Y' include and p.lastname = p2.lastname*/

Sean Lange · Accepted Answer · 2015-06-19 14:44:57Z

1

This should work assuming at least 1 value is a 'Y'. I also added datatypes to all your variables and changed them to be char(1) as varchar(1) is quite silly. Not really sure what you are trying to accomplish with this but you have what is called a triangular join (the <> predicate) which can cause really awful performance problems. http://www.sqlservercentral.com/articles/T-SQL/61539/

Also, you need stop storing SSN in clear text. It needs to be hashed with salt or at least encrypted.

Declare @lastnamedup char(1) = 'N',
        @firstnamedup char(1) = 'N',
        @DOBdup char(1) = 'N',
        @SSNdup char(1) = 'N',
        @ZIPdup char(1) = 'N'

/*UI values passed could set any to Y*/

select
   p.id
   p.lastname,
   p.firstname,
   p.SSN,
   p.DOB,
   p.Address1,
   p.Address2,
   p.zip
   p.phone
from person p
join person p2 on p.id <> p2.id
    AND
    (
        (p.lastname = p2.lastname and @lastnamedup = 'Y')
        OR
        (p.firstname = p2.firstname and @firstnamedup = 'Y')
        OR
        (p.DOB = p2.DOB and @DOBdup = 'Y')
        OR
        (p.SSN = p2.SSN and @SSNdup = 'Y')
        OR
        (p.zip = p2.zip and @ZIPdup = 'Y')
    )

edited Jun 19, 2015 at 14:44

answered Jun 19, 2015 at 14:40

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Daniel E. Over a year ago

do the separate or clauses not have to be parenthesized individually

1010 Over a year ago

@daniel No, they don't. AND has higher precedence than OR in tsql: msdn.microsoft.com/en-us/library/ms190276.aspx

Sean Lange Over a year ago

@1010 what is posted when you commented is after the fix. :)

1010 Over a year ago

yes, but those parentheses are unnecessary. a AND b OR c AND d is equivalent to (a AND b) OR (c AND d).

Daniel E. Over a year ago

@1010 cool, maybe im thinking of cases where its flipped, (A or B) AND (c OR d)

|

Tom · Accepted Answer · 2015-06-19 14:37:47Z

0

What you could do is add the joins with an or statement in which you check the value of your parameter but then you will still end up with a join. You could also build up your SQL command in a nvarchar variable (@SQL) adding the joins where needed and then perform an EXECUTE(@SQL) to get the results from the complete query.

answered Jun 19, 2015 at 14:37

Tom

7475 silver badges17 bronze badges

1 Comment

Ross Over a year ago

Thanks, I think this may be the route I have to take. I am surprised this isn't a better way to handle it in SQL

Collectives™ on Stack Overflow

Conditional Join Statements based on Variable - SQL Server

2 Answers 2

9 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related