Finding records

[Guest]

Hello,
I have three tables, one is Customer, one is Item and one is Sales.
Customers have custid which is uniqe, Items have itemid which is uniqe and
Sales has saleid which is also unique. They also have the usual fields (name
address, price etc) Customer and Sales are linked on Custid and Sale and Item
are linked on Itemid as they can only buy one item at a time.

I want to get a list of every customer who has bought a particular item, but
not this other one, for example:
Each customer who bought Paper 1 over 6 months ago, but has not yet bought
Paper 2.

Paper 1 or Paper 2 will be in Item.Name but I can type in the itemid. I just
need to find every custid that has itemid 1 but not itemid 2 in the linked
fields.

Any help would be greatly appreciated

Many thanks
James

 

[John Spencer]

One query method that may work:

SELECT C.CustID
FROM (Customers as C INNER JOIN Sales as S
ON C.CustID = Sales.CustID)
INNER JOIN Items as I
ON S.ItemID = I.ItemID
WHERE I.ItemName = "Paper 1"
AND NOT Exists(
SELECT S1.*
FROM Sales as S1 INNER JOIN Items as IB
ON S1.ItemID = IB.ItemID
WHERE S1.CustID = C.CustID
AND IB.ItemName = "Paper 2")

You could also do this with stacked queries.
--First query gets everyone that has bought paper2
--Second query uses that query in an unmatched query to get everyone that
has not bought paper 2, but has bought the other item
-- Third query uses the second query

Query - qBoughtThis
SELECT Sales.CustId
FROM Sales
WHERE ItemID = 5

Query - qGotOneNotOther
SELECT S.CustID
FROM Sales as S Left Join QBoughtThis as Q
ON S.CustId = Q.BoughtThis
WHERE S.ItemID = 8

Finally
SELECT C.CustId
FROM Customers as C INNER JOIN qGotOneNotOther as Q

 

[Guest]

Thankyou very much I will go give that a try now.

James