CODEWITHSUNDEEP
sqlsql-server
Mo-L
Mo-L

Reputation: 39

SQL Pull COLUMNS meeting specific criteria not ROWS

Table1  
Buyer   Product
John    Car
Sammy   Car
Jacob   Car
Sara    Car
Jesus   Car
Sean    Car
John    House
Sammy   House
Jacob   House
John    Boat
Sammy   Boat

My Query:

Select *
From (
    Select Distinct Buyer
    Row_Number() OVER (PARTITION BY Buyer ORDER BY Product) AS Uniqueness
    From Table1
    Where product <> 'Boat'
) As Z

Where Uniqueness =1

Results are: 

Buyer
John
Sammy
Jacob
Sara
Jesus
Sean

Results desired:

Buyer
Jacob
Sara
Jesus
Sean

I'm only looking for the PEOPLE who don't have a boat not the ROWS that don't have a boat. The reason i have the partition is because in my actual query it is needed so i wanted to include it here so any solution also takes it into consideration THANK YOU!

Upvotes: 0

Views: 39

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

One method uses aggregation:

select buyer
from t
group by buyer
having sum(case when product = 'Boat' then 1 else 0 end) = 0;  -- no boats

However, if you have a separate table of buyers, then not exists is probably more appropriate:

select b.*
from buyers b
where not exists (select 1 from t where t.buyer = b.buyer and t.product = 'Boat');

This will return buyers who have made no purchases as well. But by avoiding the outer aggregation (or distinct) it is likely to be faster with the right indexes.

Upvotes: 1

Joe C
Joe C

Reputation: 3993

Its better if you have an ID field instead of just buyer name. I will show a query that depends on Buyer being unique

Select Buyer
From Table1 T1
Left Join Table1 T2 On T1.Buyer = T2.Buyer And T2.product = 'Boat'
Where T2.Buyer Is Null

Upvotes: 0

Related Questions