Query with combined exclusion

Question

Query with combined exclusion

thomas.lemesurier

Right there with Babe

Points: 719
More actions
May 23, 2008 at 9:28 am

#207592

Hello,
I have a query where I am trying to specifically exclude records where the combination of two conditions is true. When I type it it looks like this:
SELECT
fldA, fldB, fldC
FROM
vwMyView
WHERE
(fldTimestamp >= @timeval)
AND
NOT ((location_ID = 2) AND (removed_ID = 1))
The idea here is that records where location_ID = 2 but removed_ID <> 1 will be returned, as will records where removed_ID = 1 but location_ID <>2. I want it so that only records where location = 2 and removed_ID = 1 will be excluded. When I run the query, the query is reformated/refactored to this:
SELECT
fldA, fldB, fldC
FROM
vwMyView
WHERE
(fldTimestamp >= @timeval) AND (NOT(location_ID = 2)) OR
(fldTimestamp >= @timeval) AND (NOT(removed_ID = 1))
I suspect that I have my brackets wrong in the original query. Could someone help me out? Thanks,
Tom

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

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

The query editor sometimes refactors things in a way that "makes sense" to it. From the looks of it - both statements are logically the same, so I don't see any harm in using either.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

If you had this data, what would you want returned? Assuming all 3 dates are greater than or equal to your date criteria?

fldTimestamp location_id removed_id

----------------------- ----------- -----------

2008-05-24 12:00:37.710 1 1

2008-05-25 12:00:37.710 1 2

2008-05-26 12:00:37.710 2 1

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

thomas.lemesurier Right there with Babe Points: 719 More actions · Answer 3

Hi and thanks to both of you for your replies. I have seen the 'auto refactoring' thing before and was under the impression that it didn't change the meaning of the original command. However, I suspected that maybe what I wanted my original to say and what it was actually saying might be two different things!

Jack, from your sample data I would want the first two records to be returned. Either of the conditions on their own should be fine. It's only the combination of the two conditions (where location = 2 AND removed = 1) that I want to exclude. Hope that makes sense!

So just to re-frame the question, do the two queries in my original question mean the same thing? And if so, are they doing what I wanted (as explained above)?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

thomas.lemesurier (5/23/2008)
Hi and thanks to both of you for your replies. I have seen the 'auto refactoring' thing before and was under the impression that it didn't change the meaning of the original command. However, I suspected that maybe what I wanted my original to say and what it was actually saying might be two different things!
Jack, from your sample data I would want the first two records to be returned. Either of the conditions on their own should be fine. It's only the combination of the two conditions (where location = 2 AND removed = 1) that I want to exclude. Hope that makes sense!
So just to re-frame the question, do the two queries in my original question mean the same thing? And if so, are they doing what I wanted (as explained above)?

You're looking at a NAND, in which case both notations are correct:

a NAND b = NOT (a AND b) = (NOT a) OR (NOT b)

In your case - with a third criterion:

c AND (a NAND b) = c AND ((NOT a) OR (NOT b)) = (c AND (NOT a)) OR (c AND (NOT b))

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

Based on the following test both queries are evaluated the same and return the same and desired data:

[font="Courier New"]

CREATE TABLE #vwMyView (fldTimestamp DATETIME, location_id INT, removed_id INT)

CREATE INDEX IX_Test ON #vwMyView(fldTImestamp, location_id, removed_id)

INSERT INTO #vwMyView

SELECT

GETDATE()+1,

1,

1

UNION ALL

SELECT

GETDATE()+2,

1,

2

UNION ALL

SELECT

GETDATE()+3,

2,

1

DECLARE @timeval DATETIME

SET @timeval = GETDATE()

SELECT

fldTimestamp,

location_id,

removed_id

FROM

#vwMyView

WHERE

fldTimestamp >= @timeval AND

NOT (location_ID = 2 AND removed_ID = 1)

SELECT

fldTimestamp,

location_id,

removed_id

FROM

#vwMyView

WHERE

(fldTimestamp >= @timeval) AND (NOT(location_ID = 2)) OR

(fldTimestamp >= @timeval) AND (NOT(removed_ID = 1))

[/font]

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

thomas.lemesurier Right there with Babe Points: 719 More actions · Answer 6

Matt, Jack,

Thanks, you've proved this to me conclusively, nice work

Query with combined exclusion

Cookies on SQLServerCentral