CODEWITHSUNDEEP
sqlsql-servert-sql
SOCAR
SOCAR

Reputation: 23

Microsoft SQL Server - Convert column values to list for SELECT IN

I have this (3 int columns in one table)

Int1 Int2 Int3
---------------
 1    2    3

I would like to run such query with another someTable:

SELECT * FROM someTable WHERE someInt NOT IN (1,2,3)

where 1,2,3 are list of INTs converted to a list that I can use with SELECT * NOT IN statement

Any suggestions how to achieve this without stored procedures in Micorosft SQL Server 2019 ?

Upvotes: 1

Views: 3169

Answers (2)

Thom A
Thom A

Reputation: 95759

Seems like you actually want a NOT EXISTS?

SELECT {Your Columns}
FROM dbo.someTable sT
WHERE NOT EXISTS (SELECT 1
                  FROM dbo.oneTable oT
                  WHERE sT.someInt NOT IN (oT.int1,oT.int2,oT.int3));

An alternative method would be to unpivot the data, and then use an equality operator:

SELECT {Your Columns}
FROM dbo.someTable sT
WHERE NOT EXISTS (SELECT 1
                  FROM dbo.oneTable oT
                       CROSS APPLY (VALUES(oT.int1),(oT.int2),(oT.int3))V(I)
                  WHERE V.I = sT.someInt);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270301

If you want rows in some table that are not in one of three columns of another table, then use not exists:

select t.*
from sometable t
where not exists (select 1
                  from t t2
                  where t.someint in (t2.int1, t2.int2, t2.int3)
                 );

The subquery returns a row where there is a match. The outer query then rejects any rows with a match.

Upvotes: 1

Related Questions