Harish reddy
Harish reddy

Reputation: 431

Select only Unique customers with pure category_ SQL

I want to select customers who have experienced only dine in and have never ordered online..I want to exclude customers who have experienced both dine in and ordered online

customer   type   
1          Dine-in 
2          Delivery
3          Dine-in
4          Delivery
1          Dine-in
2          Dine-in

I want a query wherein the customer with id 3 gets selected since they haven't ordered online

Upvotes: 0

Views: 35

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270593

If you want customers, I would suggest using aggregation:

select customer
from t
group by customer
having sum( type = 'Delivery' ) = 0;

Use not exists if you want the original rows. However, your question suggests that you just want the list of customers.

Upvotes: 1

Tedo G.
Tedo G.

Reputation: 1565

Use EXISTS clause

SELECT *
FROM table AS A
WHERE type = 'Dine-in'
AND NOT EXISTS (
        SELECT *
        FROM table AS B
        WHERE B.customer = A.customer
        AND B.type = 'Delivery')

Upvotes: 1

Related Questions