CODEWITHSUNDEEP
sql-serversql-server-2008
odlan yer
odlan yer

Reputation: 771

Select query using NOT IN in SQL Server 2008

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?

Upvotes: 1

Views: 633

Answers (3)

Louie Bao
Louie Bao

Reputation: 1732

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"?

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

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.

Upvotes: 3

Squirrel
Squirrel

Reputation: 24803

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

Upvotes: 0

Related Questions