Post reply

Simple SELECT for Inoices with certain Items

  • February 9, 2009 at 2:19 pm

    #199790

    Hello All,

    Not sure what my issue is...this should be straight forward.

    I have a table with invoices...each invoice can include 1 or many items.

    InvoiceID| ItemID

    ------------------------

    1| A

    1| B

    1| C

    2| A

    2| B

    2| D

    3| B

    3| C

    3| D

    4| A

    For this example I what to return any invoice that doesn't include Item "C"...Invoice 2 and 4.

    My query looks like the one below

    SELECT InvoiceID

    FROM TestTable

    WHERE 'C' NOT IN (SELECT ItemID

    FROM TestTable

    GROUP BY InvoiceID, ItemID)

    Any ideas?

    Thanks for your help,

    Jason

  • February 9, 2009 at 2:36 pm

    #940836

    try this

    select invoieid from testtable where invoieid not in (select invoiceid from testtable where itemid ='C')

  • February 9, 2009 at 2:40 pm

    #940838

    duplicate post...continue to follow the thread here:

    http://www.sqlservercentral.com/Forums/Topic653232-149-1.aspx

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 9, 2009 at 2:42 pm

    #940839

    I would use NOT EXISTS instead of NOT IN for better performance like this:

    SELECT DISTINCT a.InvoiceID

    FROM TestTable a

    WHERE NOT EXISTS

    (SELECT * FROM TestTable b

    WHERE a.InvoiceID = b.InvoiceID AND b.ItemID = 'C')

    Greg

  • February 9, 2009 at 2:46 pm

    #940841

    Oops! I posted to both! I thought that they were the same post and assumed that I accidentally closed the window the first time without posting.

    Greg

  • February 9, 2009 at 3:23 pm

    #940867

    That was it...thanks Greg!!!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply