CODEWITHSUNDEEP
sqlpostgresql
strider
strider

Reputation: 1

Postgresql Query: How to return all parent records when certain criteria for all child records are met

I'm working with a SQL table, 'contacts' that includes the fields 'contact_id', 'account_id', and 'approved'.

Accounts are companies that are potential customers, and there are multiple contact people per account. If any of those contact people are approved (meets certain criteria), we can sell to the account. The 'approved' field is boolean.

I'm trying to write a query that will return account_ids for accounts where no contact is approved. I've tried playing around with ANY, BOOL_AND and several types of grouping and counting with no success. Any suggestions are much appreciated.

My table resembles this:

ACCOUNT_ID  CONTACT_ID  APPROVED
Apple       123         TRUE
Apple       321         FALSE
Pear        456         FALSE
Pear        654         FALSE
Orange      789         TRUE
Orange      987         TRUE

I would like my query to return

ACCOUNT_ID
Pear

Because this is the only account for which all records are false.

Upvotes: 0

Views: 173

Answers (1)

Trung Duong
Trung Duong

Reputation: 3475

You could try to use NOT EXISTS 

SELECT DISTINCT account_id 
FROM contacts a
WHERE 
    NOT EXISTS (
    SELECT 1 
    FROM contacts b
    WHERE a.account_id = b.account_id AND b.approved = TRUE)

I've created a demo here

Upvotes: 1

Related Questions