CODEWITHSUNDEEP
sql
Fyzzys
Fyzzys

Reputation: 895

How can I exclude users who have only purchase with car?

There is this SQL(from Django) query:

SELECT "id", "name" 
FROM "polls_client" INNER JOIN "polls_purchases" 
ON ("id" = "client_id") 
WHERE "polls_purchases"."product" IN (car, bike)

We need to select from query users who have purchase records only 'car'. I want to do this in one select to the database. How do I do this?

Upvotes: 1

Views: 102

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

We need to select from query users who have purchase records only 'car'.

The simplest, most efficient method should be not exists:

SELECT c.*
FROM "polls_client" c
WHERE NOT EXISTS (SELECT 1
                  FROM "polls_purchases" pp
                  WHERE c."id" = pp."client_id" AND pp."product" <> 'car'
                 );

In particular, this can take advantage of an index on polls_purchases(client_id, product).

I would also dissuade your from using double quotes for identifies. They only serve to clutter queries.

Upvotes: 1

forpas
forpas

Reputation: 164174

You can group by client and set the condition in the HAVING clause:

SELECT pc.id, pc.name 
FROM polls_client pc INNER JOIN polls_purchases pp 
ON pc.id = pp.client_id 
GROUP BY pc.id, pc.name
HAVING SUM(CASE WHEN pp.product <> 'car' THEN 1 ELSE 0 END) = 0

Upvotes: 1

Related Questions