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.

score 1 · Answer 1 · answered May 29 '12 at 09:38

1

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

answered May 29 '12 at 09:38

Andrius Naruševičius

8,348
7
49
78

score 1 · Answer 2 · answered May 29 '12 at 10:28

1

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

answered May 29 '12 at 10:28

levi

3,451
6
50
86

can you explain a bit more extensively your answer? tnx – Daniele B May 29 '12 at 23:13
I make left join with the same table on id and a.seller='Seller A'. If the buyer has purchased smth, b.id will have some mean else it will be null, that last we need – levi May 30 '12 at 07:59

Sachin Kainth · Answer 3 · 2012-05-29T09:37:08.457

0

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.

edited May 29 '12 at 09:37

answered May 29 '12 at 09:36

Sachin Kainth

45,256
81
201
304

score 0 · Answer 4 · answered May 29 '12 at 13:41

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

score 0 · Answer 5 · answered May 29 '12 at 14:10

0

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

answered May 29 '12 at 14:10

Andriy M

76,112
17
94
154

Filtering distinct rows in SQL

5 Answers5

Linked