how to use 'NoT IN' and 'IN' at same time in Query

Question

I have Two Tables Let Say "Table1" and "Table2"

Table1

ID          AccountId  BranchId    otherColumn
----------- ---------- ---------- -----------
1           15           58          data
2           22           62          data
3           31           89          data
4           49           45          data
.           .            .           .
.           .            .           .
.           .            .           .
.           .            .           .
.           .            .           .

Table2

ID          fromAccount toAccount ExcludeAccount  IncludeAccount   FromBranch  ToBranch     IncludeBranch  ExcludeBranch
----------- ---------- ---------- -----------     ----------       ---------   ------------ -------------- ------------
1           1          90          89,34,3        101              30          100          205,207,250     35,40
1           5          67                         90               22          50           70,90           20
2           7          4           3                               5           200                          
2           1          5                                           7           10           16              9
3           5          89          6,7            200              55          243          34              35,200,201,234

Now I want to select All data from Table1 using expression from Table1

I have Function that converts the comma separated text to table

select data from dbo.split('23,45,2', ',')

this will return

 data
 ------
 23
 45
 2

My Desired Output for row 1 is

    ID          AccountId  BranchId    otherColumn
    ----------- ---------- ---------- -----------
    .           1           .          data
    .           2           .          data
    .           4           .          data
    .           5           .          data
    .           6           .          data
    .           7           .          data
    .           8           .          data
    .           .           .             .
    .           .           .             .
    .           33          .             .
    .           35          .             .
    .           .           .             .
    .           88          .             .
    .           90          .             .
    .           101         .             .
    .            .          30            .
    .            .          31            .
    .            .           .            .
    .            .          34            .
    .            .          36            .
    .            .           .            .

I have created query to get data with relation of these two table But it always returns no row

here is my query

select * from Table1
inner join Table2 on Table1.AccountId between Table2.fromAccount and Table2.toAccount
and Table1.AccountId not in (select data from dbo.split(Table2.ExcludeAccount, ','))
and Table1.AccountId in (select data from dbo.split(Table2.IncludeAccount, ','))
and Table1.BranchId between Table2.FromBranch and Table2.ToBranch
and Table1.BranchId not in (select data from dbo.split(Table2.ExcludeAccount, ','))
and Table1.BranchId in (select data from dbo.split(Table2.IncludeAccount, ','))

my question is, why it always returns no data is any thing wrong in my query or i am doing it in wrong way

Answer 1

not in is generally not what you want with a subquery. If any of the values returned by the subquery are NULL, then nothing at all passes the filter. Instead, get used to using not exists.

For instance, instead of:

not in (select data from dbo.split(Table2.ExcludeAccount, ','))

Use:

not exists (select 1
            from dbo.split(Table2.ExcludeAccount, ',') s(p)
            where Table1.BranchId = s.p
           )

You may also have a problem with data types, but SQL Server should be converting them correctly.