Let's say I have an array of values, (A,B,C,D) and a SQL Server table (table1) with a column (field1).
What I want to do is construct a query such that each value selected is not found in table1.field1.
For example, if A and D are found in table1.field1 then the only values that would be selected would be B and C.
What would such a query look like?
Thank you,
Elliott
You can try,
select field1 from table1 where field1 not in('A','D')
If you want something different, Please comment.
If you need "pure" T-SQL it's very easy. Look - to all intents and purposes you just have 2 multitudes:
(A,D)
and
(A,B,C,D)
What you want is "all from second, but not from one". Operator EXCEPT is you answer (that is - logical difference of multitudes):
USE tempdb
go
CREATE TABLE T1 (col1 char(1))
go
INSERT T1 VALUES ('A')
INSERT T1 VALUES ('D')
go
CREATE TABLE T2 (col2 char(1))
go
INSERT T2 VALUES ('A')
INSERT T2 VALUES ('B')
INSERT T2 VALUES ('C')
INSERT T2 VALUES ('D')
SELECT * FROM T2
EXCEPT
SELECT * FROM T1
Result:
col2
B
C
