CODEWITHSUNDEEP
sqlsql-serverwhere-clause
Benzz
Benzz

Reputation: 119

How to query multiple where 'NOT Equal' conditions

I am trying to work out how to pull back records where "Field A" and "Field B" cannot be a set combination.

Example: Field A cannot equal "Scheduled" whilst Field B Equals "PreliminaryScheduled"

But I do want to see other records where Field A = "Scheduled" and Field B = "PreliminaryScheduled"

I hope this makes sense, please see the script below, I have included a very basic temp table created with examples of what I am trying to achieve, I havea workaround by using a CONCAT but I don't think this is the best solution?

I know I can exclude these by doing a where not exists but in the actual database this would be a big table and I would prefer not to double query this.

I have a work around also but I would like to know if there is a proper/better way of doing this task.

Please see the code and comments.

    --=============================
-- Create Table
--=============================
CREATE TABLE #Temp
(
[id] INT IDENTITY(1,1),
[status] nvarchar(100),
[fkstatus] NVARCHAR(200),
[Date] DATE
)

--=============================
-- Insert Into Table
--=============================
INSERT INTO [#Temp]
(
    [status],
    [fkstatus],
    [Date]
)
VALUES
(N'Scheduled',  N'PreliminaryScheduled', GETDATE()), (N'Scheduled',  N'PreliminaryScheduled', '2019-01-01'), (N'Cancelled',  N'PreliminaryScheduled', '2019-02-01'), (N'Complete',  N'PreliminaryScheduled', GETDATE()), (N'Scheduled',  N'Other', '2019-03-01')

--=============================
--(A)
-- Brings back what I DO NOT want, these are the items that I want to exclude.
--=============================
SELECT * 
FROM [#Temp]
WHERE ([status] = 'Scheduled'  AND [fkStatus] = 'PreliminaryScheduled')

--=============================
-- (B)
-- Real world logic, I beleive this should work?.....
--=============================
SELECT * 
FROM [#Temp]
WHERE ([status] <> 'Scheduled' AND [fkStatus] <> 'PreliminaryScheduled')

--=============================
-- (C)
-- Work Around - Or is this the actual way this has to be done?
--=============================
SELECT * 
FROM [#Temp]
WHERE CONCAT([status],'-',[fkstatus]) <> 'Scheduled-PreliminaryScheduled'

--=============================
-- (D)
-- Additional with a Date.
--=============================
SELECT * 
FROM [#Temp]
WHERE  ([status] <> 'Scheduled' or [fkStatus] <> 'PreliminaryScheduled')
AND [Date] < '2019-01-01'

--I expect this to return results from Point (C) but with (D) in mind.

Upvotes: 1

Views: 12012

Answers (3)

Randolph
Randolph

Reputation: 302

You can use the NOT statement.

SELECT * FROM [#Temp]
    WHERE [Date] < '2019-01-01'
        AND NOT ([status] = 'Scheduled'  AND [fkStatus] = 'PreliminaryScheduled')

Upvotes: 2

D-Shih
D-Shih

Reputation: 46239

You can try to use OR instead of AND

SELECT * 
FROM [#Temp]
WHERE  ([status] <> 'Scheduled' or [fkStatus] <> 'PreliminaryScheduled')

Upvotes: 3

MarcinJ
MarcinJ

Reputation: 3639

Negate the condition you don't want records to meet using NOT:

SELECT * FROM #temp 
WHERE NOT ([status] = 'Scheduled'  AND [fkStatus] = 'PreliminaryScheduled')

Alternatively, use OR:

SELECT * FROM #temp 
WHERE [status] <> 'Scheduled'  OR [fkStatus] <> 'PreliminaryScheduled'

They both result in the same query plan (using OR), you might find the first one is a bit clearer.

Upvotes: 1

Related Questions