How do I add an exclusion to a query?

Question

Post reply

How do I add an exclusion to a query?

bkufleitner

SSC Enthusiast

Points: 178
More actions
March 30, 2022 at 7:35 pm

#4008443

I have a query that pulls several different columns/attributes of results
Two of those columns in the results of my query are EMPLOYEE_ID and WORKSITE_ID
I DON'T want the following to show up in my results when i run my query:
If EMPLOYEE_ID = 123 AND WORKSITE_ID=22
How do I add a line or two to my query to exclude anything related to that that combination from showing up in my results?
Thanks!

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

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

pietlinden SSC Guru Points: 63503 More actions · Answer 1

SELECT ...

FROM t

WHERE (EMPLOYEE_ID != 123 AND WORKSITE_ID != 22)

ratbak Ten Centuries Points: 1358 More actions · Answer 2

If it's only that combination we want to exclude, I think we'd want

SELECT ...

FROM t

WHERE NOT (EMPLOYEE_ID = 123 AND WORKSITE_ID = 22)

(EMPLOYEE_ID != 123 AND WORKSITE_ID != 22) would exclude rows that contain either value, even in other combinations.

DROP TABLE IF EXISTS #Work;

CREATE TABLE #Work
    (EMPLOYEE_ID INT NOT NULL,
    WORKSITE_ID INT NOT NULL);

INSERT INTO #Work 
        (EMPLOYEE_ID,WORKSITE_ID)
VALUES  (123,21),
        (123,22),
        (123,23),
        (122,21),
        (123,22),
        (124,23);

SELECT * FROM #Work WHERE (EMPLOYEE_ID != 123 AND WORKSITE_ID != 22);
SELECT * FROM #Work WHERE NOT (EMPLOYEE_ID = 123 AND WORKSITE_ID = 22);

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

I think I read that != may not be supported in the future, and <> is recommended ANSI standard

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

pietlinden wrote:

SELECT ... FROM t WHERE (EMPLOYEE_ID != 123 AND WORKSITE_ID != 22)

The logical equivalent of

NOT (A AND B)

is

NOT A OR NOT B

so changing the AND in your query to an OR does the trick. But the NOT (A AND B) version is easier to get your head round, in my opinion. I also agree with homebrew that <> is to be preferred over !=.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

juddtrumps Newbie Points: 1 More actions · Answer 5

To exclude text, use the "Not" criteria followed by the word or phrase you want to exclude. Displays contacts in all the cities except Boise. Displays all contacts that are not in Boise or New York or Las Vegas. Tip: Not Like "X*" finds all items except those starting with the specified letter.

Steve Collins SSCrazy Points: 2157 More actions · Answer 6

Phil Parkin wrote:

The logical equivalent of
NOT (A AND B)
is
NOT A OR NOT B

It's De Morgan's Law. The negation of a conjunction is the disjunction of the negations

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können