Select query using NOT IN in SQL Server 2008

Question

I have two tables Sales and Return:

Sales table:

DocNum         ItemCode
101           itemcode1
101           itemcode2
102           itemcode3
102           itemcode2

Return table:

DocNum         ItemCode
101            itemcode1
102-reject     itemcode2

Desired output:

DocNum         ItemCode
101           itemcode2
102           itemcode3

I need to select data from the Sales table that does not exist in the Return table, using a NOT IN condition. I only get records that match DocNum column on the two tables, my problem here is user put a word 'reject' on the Return table.

Is there a way to to match the docnum column in these situation?

what is your existing NOT IN query like ? What is your matching criteria ? — Squirrel
– Squirrel, Commented Feb 24, 2016 at 3:21

Tim Biegeleisen · Accepted Answer · 2016-02-24 03:26:19Z

3

You can achieve what you want using a LEFT JOIN:

SELECT s.DocNum
FROM Sales s LEFT JOIN Return r
    ON s.DocNum = CASE WHEN CHARINDEX('-', r.DocNum) > 0
                     THEN SUBSTRING(r.DocNum, 1, CHARINDEX('-', r.DocNum)-1)
                     ELSE r.DocNum
                  END AND
        s.ItemCode = r.ItemCode
WHERE r.DocNum IS NULL

By the way, you should rethink your database design and stop putting the word "reject" into an id column, which makes querying the table difficult. Instead, add a new boolean column called reject to keep track of this.

edited Feb 24, 2016 at 3:26

answered Feb 24, 2016 at 3:13

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

odlan yer Over a year ago

Thanks it works!, And for your suggestion, I cannot do it, This is an old system I don't have the source code to control user input, BTW Thank you so much.

Squirrel · Accepted Answer · 2016-02-24 02:59:38Z

0

SELECT *
FROM   Sales s
WHERE  NOT EXISTS
       (
            SELECT *
            FROM   [Return] r
            WHERE  r.DocNum LIKE s.DocNum + '%'
       )

answered Feb 24, 2016 at 2:59

Squirrel

24.8k5 gold badges41 silver badges37 bronze badges

2 Comments

Felix Pamittan Over a year ago

@odlanyer, just add AND r.ItemCode = s.ItemCode in the WHERE clause (the one inside the NOT EXISTS).

Tim Biegeleisen Over a year ago

I think your WHERE clause might match 102 to 1023-reject.

Louie Bao · Accepted Answer · 2016-02-24 03:48:37Z

As far as the question is concerned, the query below should return the expected output, even if the DocNum is set to something like "102/reject" or "102(reject)" or whatever.

select a.DocNum, a.ItemCode
from SalesTable a
left join ReturnTable b     on charindex(a.DocNum, b.DocNum) > 0
                                and a.ItemCode = b.ItemCode
where b.DocNum is null

However, as far as sensible table design is concerned, allowing DocNum to be updated to 102-reject is a bad practice. This exposes a few design flaws:

DocNum is a varchar column instead of a more suitable integer column
Allowing user to manipulate what is obviously a key column may introduce SQL injection risks.
and what happens when DocNum is updated to "102-101"?

Collectives™ on Stack Overflow

Select query using NOT IN in SQL Server 2008

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related