WHERE Clause comparing more than 2 columns

Question

Post reply

WHERE Clause comparing more than 2 columns

Tania Mofflin

Ten Centuries

Points: 1021
More actions
May 16, 2014 at 2:06 am

#286768

Hello
This is part of a much more complex query but I will just include the columns that are of relevance as I think it can be answered without complicating it.
There are 4 tables which contain branch information and a common Equipment ID across all tables which I need to make sure all 4 branches align.
The results of the query would look something like
Equipment ID Equipment Branch Asset Branch Service Branch Agreement Branch
CAG001 BRISBANE BRISBANE ADELAIDE BRISBANE
CAG002 TOWNSVILLE TOWNSVILLE TOWNSVILLE TOWNSVILLE
CAG003 PERTH GERALDTON GERALDTON GERALDTON
CAG004 BRISBANE PERTH PERTH PERTH
CAG005 PERTH PERTH PERTH PERTH
CAG006 PERTH KARRATHA PERTH KARRATHA
Basically all 4 branches should match and I want to filter out any line where they do in the case CAG002 and CAG005
I know how to do a where clause to compare 2 columns (let's say they are T1-T4) like
where T1.Branch <> T2.Branch
[/Code]
but I don't know how to compare all 4 at once without doing some long string of OR's.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

How about this:

WHERE NOT((T1.Branch = T2.Branch)

AND (T1.Branch = T3.Branch)

AND (T1.Branch = T4.Branch)

)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 2

Koen Verbeeck (5/16/2014)
How about this:
WHERE NOT((T1.Branch = T2.Branch)
AND (T1.Branch = T3.Branch)
AND (T1.Branch = T4.Branch)
)

Following the De Morgan lows, this is practically the same as

WHERE (T1.Branch != T2.Branch) OR (T1.Branch != T3.Branch) OR (T1.Branch != T4.Branch)

regarding the the precedence of NOT is before the precedence of AND, so ORs replaced with ANDs, but only visually. In execution plan nothing will be changed.

Regards,

Igor

Igor Micev,My blog: www.igormicev.com

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

This seems like a perfect situation to use EXCEPT. While the solution posted by Koen and the alternate yet just as viable solution from Igor will both work they both have the disadvantage of being nonSARGable. Basically it will have to perform an index scan for each table. If you instead use EXCEPT you can keep your logic SARGable and return the same thing.

You did not post any ddl or sample data but since you are brand new around here I did this for you. This is something you should do on future posts.

First the ddl and sample data.

create table Branch

(

BranchID int identity primary key clustered,

EquipmentID char(6),

BranchName varchar(25),

BranchType varchar(15)

)

create nonclustered index ix_Branch_BranchName on Branch(BranchName)

insert Branch

select 'CAG001', 'BRISBANE', 'Equipment' union all

select 'CAG002', 'TOWNSVILLE', 'Equipment' union all

select 'CAG003', 'PERTH', 'Equipment' union all

select 'CAG004', 'BRISBANE', 'Equipment' union all

select 'CAG005', 'PERTH', 'Equipment' union all

select 'CAG006', 'PERTH', 'Equipment' union all

select 'CAG001', 'BRISBANE', 'Asset' union all

select 'CAG002', 'TOWNSVILLE', 'Asset' union all

select 'CAG003', 'GERALDTON', 'Asset' union all

select 'CAG004', 'PERTH', 'Asset' union all

select 'CAG005', 'PERTH', 'Asset' union all

select 'CAG006', 'KARRATHA', 'Asset' union all

select 'CAG001', 'ADELAIDE', 'Service' union all

select 'CAG002', 'TOWNSVILLE', 'Service' union all

select 'CAG003', 'GERALDTON', 'Service' union all

select 'CAG004', 'PERTH', 'Service' union all

select 'CAG005', 'PERTH', 'Service' union all

select 'CAG006', 'PERTH', 'Service' union all

select 'CAG001', 'BRISBANE', 'Agreement' union all

select 'CAG002', 'TOWNSVILLE', 'Agreement' union all

select 'CAG003', 'GERALDTON', 'Agreement' union all

select 'CAG004', 'PERTH', 'Agreement' union all

select 'CAG005', 'PERTH', 'Agreement' union all

select 'CAG006', 'KARRATHA', 'Agreement'

I am kind of guessing on the layout of stuff but this seems reasonable close. Now let's take a look at the possible solutions.

--Koen

select *

from Branch b1

join Branch b2 on b2.EquipmentID = b1.EquipmentID and b2.BranchType = 'Asset'

join Branch b3 on b3.EquipmentID = b1.EquipmentID and b3.BranchType = 'Service'

join Branch b4 on b4.EquipmentID = b1.EquipmentID and b4.BranchType = 'Agreement'

where b1.BranchType = 'Equipment'

and NOT((b1.BranchName = b2.BranchName)

AND (b1.BranchName = b3.BranchName)

AND (b1.BranchName = b4.BranchName)

)

--Igor

select *

from Branch b1

join Branch b2 on b2.EquipmentID = b1.EquipmentID and b2.BranchType = 'Asset'

join Branch b3 on b3.EquipmentID = b1.EquipmentID and b3.BranchType = 'Service'

join Branch b4 on b4.EquipmentID = b1.EquipmentID and b4.BranchType = 'Agreement'

where b1.BranchType = 'Equipment'

and ((b1.BranchName != b2.BranchName)

OR (b1.BranchName != b3.BranchName)

OR (b1.BranchName != b4.BranchName)

)

--Sean

select *

from Branch b1

join Branch b2 on b2.EquipmentID = b1.EquipmentID and b2.BranchType = 'Asset'

join Branch b3 on b3.EquipmentID = b1.EquipmentID and b3.BranchType = 'Service'

join Branch b4 on b4.EquipmentID = b1.EquipmentID and b4.BranchType = 'Agreement'

where b1.BranchType = 'Equipment'

EXCEPT

select *

from Branch b1

join Branch b2 on b2.EquipmentID = b1.EquipmentID and b2.BranchType = 'Asset'

join Branch b3 on b3.EquipmentID = b1.EquipmentID and b3.BranchType = 'Service'

join Branch b4 on b4.EquipmentID = b1.EquipmentID and b4.BranchType = 'Agreement'

where b1.BranchType = 'Equipment'

and b1.BranchName = b2.BranchName

AND b1.BranchName = b3.BranchName

AND b1.BranchName = b4.BranchName

All three queries return the same results. The first two use index scans to filter out the rows where the last one can use index seeks for everything.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Here's another guess;

DROP TABLE #Equipment;CREATE TABLE #Equipment ([Equipment ID] VARCHAR(10), [Equipment Branch] VARCHAR(20))

INSERT INTO #Equipment ([Equipment ID], [Equipment Branch])

VALUES ('CAG001','BRISBANE'),('CAG002','TOWNSVILLE'),('CAG003','PERTH'),('CAG004','BRISBANE'),('CAG005','PERTH'),('CAG006','PERTH')

DROP TABLE #Asset;CREATE TABLE #Asset ([Equipment ID] VARCHAR(10), [Asset Branch] VARCHAR(20))

INSERT INTO #Asset ([Equipment ID], [Asset Branch])

VALUES ('CAG001','BRISBANE'),('CAG002','TOWNSVILLE'),('CAG003','XXXX'),('CAG004','BRISBANE'),('CAG005','PERTH'),('CAG006','PERTH')

DROP TABLE #Service;CREATE TABLE #Service ([Equipment ID] VARCHAR(10), [Service Branch] VARCHAR(20))

INSERT INTO #Service ([Equipment ID], [Service Branch])

VALUES ('CAG001','BRISBANE'),('CAG002','TOWNSVILLE'),('CAG003','PERTH'),('CAG004','XXXX'),('CAG005','PERTH'),('CAG006','PERTH')

DROP TABLE #Agreement;CREATE TABLE #Agreement ([Equipment ID] VARCHAR(10), [Agreement Branch] VARCHAR(20))

INSERT INTO #Agreement ([Equipment ID], [Agreement Branch])

VALUES ('CAG001','BRISBANE'),('CAG002','TOWNSVILLE'),('CAG003','PERTH'),('CAG004','BRISBANE'),('CAG005','XXXX'),('CAG006','PERTH')

SELECT e.[Equipment ID], e.[Equipment Branch], a.[Asset Branch], s.[Service Branch], g.[Agreement Branch]

FROM #Equipment e

LEFT JOIN #Asset a

ON a.[Equipment ID] = e.[Equipment ID]

AND a.[Asset Branch] = e.[Equipment Branch]

LEFT JOIN #Service s

ON s.[Equipment ID] = e.[Equipment ID]

AND s.[Service Branch] = e.[Equipment Branch]

LEFT JOIN #Agreement g

ON g.[Equipment ID] = e.[Equipment ID]

AND g.[Agreement Branch] = e.[Equipment Branch]

WHERE a.[Asset Branch] IS NULL OR s.[Service Branch] IS NULL OR g.[Agreement Branch] IS NULL

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Tania Mofflin Ten Centuries Points: 1021 More actions · Answer 5

Thanks for the responses, my skills are developing each day but the other responses at this stage are a bit complex for me to follow. This one works for me.