Re: Brain Dead on SQL statement

Mark A. Smith wrote:
Typical Customer, Orders, Items table layout. Which Customers HAVE NOT purchased Item3, But HAVE purchased Item1 OR item2?

TIA


Tested against an Access 2002 database. Seems to work, but I would not be surprised if there were a faster way.

SELECT DISTINCT People.ID
FROM (People INNER JOIN Transactions ON People.ID=Transactions.ID)
INNER JOIN Purchases ON
Transactions.Invoice_Number=Purchases.Invoice_Number
WHERE Purchases.Item_Code in ('WFD','COMP')
AND People.ID NOT IN
(SELECT People.ID
FROM (People INNER JOIN Transactions ON People.ID=Transactions.ID)
INNER JOIN Purchases ON
Transactions.Invoice_Number=Purchases.Invoice_Number
WHERE Purchases.Item_Code='HRU')

--
Jon Purvis
Wildlife Technical Programs Lead
Texas Parks and Wildlife Department
Austin, TX
.