Simple SELECT for Invoices with certain Items

• Jason Leadbetter

  SSC-Addicted

  Points: 423

  More actions

  February 9, 2009 at 2:21 pm

  #383899

  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

• JestersGrind

  SSCertifiable

  Points: 5439

  More actions

  February 9, 2009 at 2:34 pm

  #940834

  I would use NOT EXISTS instead of NOT IN 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