From a recent email:
Steve,
Need to run a query in SQL to find customers who have not purchased a particular product. In this case “FNADE*”
I can easily query customers who have purchased a particular or range of products, but never had to query a list of customers who have not purchased a particular product…
Can you get me going in the right direction?
Thanks.
Answer:
This is a tricky one. There are several ways to do it, but this seems simplest:
First, get a list of customers that HAVE gotten a product
select distinct custnmbr
from sop30200 h
join sop30300 l on l.sopnumbe = h.sopnumbe and h.soptype = l.soptype
where itemnmbr like 'SERVICE, HOURLY'
Then, get everyone else (note the query above is used in the where clause below. )
select custnmbr
from rm00101 r
where custnmbr not in (
select distinct custnmbr
from sop30200 h
join sop30300 l on l.sopnumbe = h.sopnumbe and h.soptype = l.soptype
where itemnmbr like 'SERVICE, HOURLY'
)