CODEWITHSUNDEEP
sqlsql-serverfilter
user3306972
user3306972

Reputation: 17

In SQL Server, how to filter lots of elements across multiple columns

I have a table, t1, with columns such as name, code1, code2,..., code20

There are, say, 100K rows.

I have another look up table, t2, which has one column, code; it has 10k rows and each row has a code. So, totally there are 10K codes in this 1-column table.

I need to filter out all the rows in t1 that have the codes in t2 from any column, i.e. columns code1 to code20. In other words, in each row in t1, once a column has one of the codes in t2, it should be captured.

Is there an easy way to do this? Thanks a lot!

Upvotes: 0

Views: 53

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Here is a way to do it using not exists:

select t1.*
from t1
where not exists (select 1
                  from t2
                  where t2.code = t1.code1 or
                        t2.code = t1.code2 or
                        . . .
                        t2.code = t1.code20
                 );

It is tempting to use in as the condition in the nested select, but this behaves in a funky way with NULLs. The sequence of direct comparisons is easier.

That said, having 20 columns with the same type of data is usually a sign of poor table design. More typically, the data would be in some sort of association/junction table, with the 20 columns each appearing in their own row.

Upvotes: 1

Johnny Fitz
Johnny Fitz

Reputation: 542

Sounds like you need to pivot the data in Table t1 then join on t2. So instead of t1 where you have name, code1, code2,...Code 20 you would pivot t1 to just Name and Code columns then join on t2. Alternatively you could just perform separate joins of t1 on t2 for each of t2's columns Code 1 to 20 and union the result. That's if I understand your problem correctly.

Upvotes: 0

Related Questions