Filtering distinct rows in SQL

Question

I have a table which includes the columns 'buyer', 'seller' and 'buyer_spend'. Each purchase is shown as a separate row with the buyer name, the seller name and the amount that's been bought.

Is it possible to use a single query to show all the distinct buyers that have not purchased from a seller?

For example if I have 'Seller A', I want to find all of the distinct Buyers that have not purchased from Seller A. I'm having trouble because the following query return all transactions that were not for Seller A, which includes many Buyers that have indeed purchased from Seller A on another row.

SELECT DISTINCT buyer WHERE seller!='Seller A';

This is probably quite straightforward, but I'm struggling to get my head round it.

Many thanks in advance.

Answer 1

SELECT DISTINCT buyer FROM table WHERE buyer NOT IN 
    (SELECT DISTINCT buyer FROM table WHERE seller='Seller A')

Answer 2

SELECT a.buyer FROM table a WHERE Left Join table as b
on a.id = b.id and a.seller='Seller A'
WHERE b.id is null
group by a.buyer

Answer 3

I guess you want something like this

select distinct buyer from purchasetable where buyer not in (select buyer from buyertable)

Although, I am a SQL Server developer, I think this will still apply.

Answer 4

Here is an alternative approach that doesn't use a join:

select buyer
from (select buyer, max(case when seller = 'Seller A' then 1 else 0 end) as hasSellerA
      from t
      group by buyer
     ) t
where hasSellerA = 0

In my experience, an aggregation query often performs better than an anti-join (not-in joins are anti-joins).

Answer 5

SELECT buyer
FROM atable
GROUP BY buyer
HAVING COUNT(seller = 'Seller A' OR NULL) = 0
  /* or: SUM(seller = 'Seller'A') = 0 */