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

@Ken White i was selected MySQL because Stack Overflow was suggesting me to select MySQL — Dexter, Nov 30 '16 at 23:32
No. StackOverflow suggested you add the **relevant DBMS** tag (*such as MySQL or SQL Server*) when you added just SQL. It did **not** suggest that you randomly grab tags that do not apply to your question. Again, use **only** the tags that actually apply to your question - it makes a big difference when dealing with SQL questions. — Ken White, Nov 30 '16 at 23:34
you walked into this one when you decided to store csv in columns. http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574 — e4c5, Nov 30 '16 at 23:43
So given your example data what would be your desired output? When an Include Account is present should only that account be returned? Also how can you include Account 101 when from & to is 1 to 90? Or is Include Account Mean Include additional Accounts? Actually same problem with your other example data too as Exclude Branch isn't even in the range. Perhaps you can clean up your example data give a desired result based on upon the example and expand on when to include or exclude. And what to do when it is a NULL in one of the exclude and include columns — Matt, Nov 30 '16 at 23:44
OK, I am editing my question so you can understand it more clearly — Dexter, Nov 30 '16 at 23:50

score 1 · Answer 1 · answered Dec 01 '16 at 00:07

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.

"If any of the values returned by the subquery are NULL..." -- Instead, get used to avoiding nulls. — onedaywhen, Dec 01 '16 at 08:43

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

1 Answers1