List of customers that have NOT bought an item

[wickyd]

Good day folx

I have a table called DEBTORS, which lists all the debtors by an account number (ACCOUNT - string) and name (NAME - string).

There are two tables which are used to track sales, STOCTRAN and STOCHIST. Both tables have identical structure. STOCTRAN is sales for the current month, and STOCHIST is sales from day one up to the last stock month end. The CODE column in these tables refers to the item that has been sold.

Table DEBTORS, column ACCOUNT = Table STOCHIST/STOCTRAN, column DEBTOR.

I want to generate a list of customers that have NOT bought a particular item. Therefore, the results from the STOCHIST/STOCTRAN query, which lists all the customers that have bought an item, must be "SUBTRACTED" from the list of debtors in the DEBTORS table and only the resultant debtors must be listed.

Any help would be greatly appreciated.

 

[swampBoogie]

select d.* 
debtors as d left join stockhist as s
on d.account = s.debtor
where s.debtor is null

 

[wickyd]

Hi swampBoogie

With a few mods here and there, your solution worked.

Thank you.