SQL Server (T-SQL)

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'

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'
      )