How to get only parent records which have all child records with same field value-CodePudding

A record in documents table can have values for 'status' field as "VERIFIED" or "REQUESTED". How we can select only 'invites' records for which all the related 'documents' records have 'status' field value as "VERIFIED" and not "REQUESTED"

CodePudding user response：

You can use a count over a case expression to check how many "VERIFIED" or "REQUESTED" values are in the table:

SELECT *
FROM   documents i
JOIN   (SELECT   id
                 COUNT(CASE status WHEN 'VERIFIED' THEN 1 END) AS count_verified,
                 COUNT(CASE status WHEN 'REQUESTED' THEN 1 END) AS count_requested
        FROM     invites
        GROUP BY id) i ON d.invite_id = i.id AND
                          count_verified > 0 AND
                          count_requested = 0

CodePudding user response：

I have found a solution with EXCEPT (MINUS) SQL construct

(SELECT invites.id, invites.invited_by FROM invites 
INNER JOIN documents
ON invites.id=documents.invite_id
GROUP BY (invites.id)
)
EXCEPT
(SELECT invites.id, invites.invited_by  FROM invites 
INNER JOIN documents
ON invites.id=documents.invite_id
WHERE documents.status='REQUESTED'
GROUP BY (invites.id)
))