March 30, 2022 at 7:35 pm
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!
March 30, 2022 at 7:57 pm
SELECT ...
FROM t
WHERE (EMPLOYEE_ID != 123 AND WORKSITE_ID != 22)
March 30, 2022 at 8:34 pm
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);
March 30, 2022 at 9:56 pm
I think I read that != may not be supported in the future, and <> is recommended ANSI standard
March 31, 2022 at 10:39 am
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 !=.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 23, 2022 at 12:57 pm
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.
June 23, 2022 at 1:44 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply