Sorry I can't think of a better Title,

I have an Invoice Table, Where The Structure is:

InvoiceID, ProductID, Qty, Sum, Location, etc

Primary Key is InvoiceID + ProductID

A single Invoice can have more than one Row so a single invoice will look like this:

InvoiceID | ProductID | Qty |  Sum  | Location
  1000    |    123    |  2  | 20.00 |   Loc1
  1000    |    321    |  4  | 12.00 |   Loc1
  1000    |    145    |  1  | 03.50 |   Loc1
  1000    |    134    |  1  | 10.00 |   Loc1
  1001    |    123    |  1  | 20.00 |   Loc1
  1002    |    321    |  1  | 03.50 |   Loc1
  1002    |    145    |  1  | 10.00 |   Loc1
  1002    |    134    |  1  | 20.00 |   Loc1

So What I want to do is write an Sql statement to exclude all Distinct InvoiceID which have the ProductID say 123.

So the result would come out to be

1002


I have a feeling this is not possible, so if someone can help me in any way would really appreciate.

Edited 6 Years Ago by finito: n/a

I figured it out for those Who need something similar

SELECT Distinct InvoiceID
FROM Invoice
WHERE InvoiceID not in (SELECT Distinct InvoiceID from Invoice where ProductID = 123)

Enjoy :)

This question has already been answered. Start a new discussion instead.