I'm writing a query in SQL Server, and I have a task to filter first table:
c1 | c2 | c3 | c4 |
---|---|---|---|
40 | 161 | 425 | 702 |
40 | 161 | 719 | 791 |
45 | 161 | 719 | 791 |
45 | 700 | 719 | 791 |
47 | 700 | 719 | 791 |
With data from second table:
c1 | c2 | c3 | c4 |
---|---|---|---|
40 | NULL | NULL | NULL |
45 | 161 | NULL | NULL |
The issue is, data in second table have "wildcards" - if there's NULL in column, than it can be any integer. Long story short, filtered table should look something like this:
c1 | c2 | c3 | c4 |
---|---|---|---|
45 | 700 | 719 | 791 |
47 | 700 | 719 | 791 |
I can probably end up with way to filter that with loop, but it seems don't be optimised at all.
To further clarify - the second table has "mask rules" to apply to the first table. Mask rules are going from c1 to c4:
One possibility is to use NOT EXISTS
and then check for your filter conditions replacing null with the value from table1 so it matches e.g.
select *
from t1
where not exists (
select 1
from t2
where t1.c1 = t2.c1
and (t1.c2 = isnull(t2.c2, t1.c2))
and (t1.c3 = isnull(t2.c3, t1.c3))
and (t1.c4 = isnull(t2.c4, t1.c4))
);
Note: This assumes that c1
cannot be null.
Returns:
c1 | c2 | c3 | c4 |
---|---|---|---|
45 | 700 | 719 | 791 |
47 | 700 | 719 | 791 |