Select the rows where the field in the join table is the same in each row.

Question

Suppose I have the following tables:

CREATE TABLE parents ( id int primary key ); CREATE TABLE children ( parent_id int, --id from parents day int, status bool, } INSERT INTO parents (id) VALUES (1); INSERT INTO children (parent_id, day, status) VALUES (1, 1, TRUE); INSERT INTO children (parent_id, day, status) VALUES (1, 2, TRUE); INSERT INTO parents (id) VALUES (2); INSERT INTO children (parent_id, day, status) VALUES (2, 1, TRUE); INSERT INTO children (parent_id, day, status) VALUES (2, 2, FALSE); INSERT INTO parents (id) VALUES (3); INSERT INTO children (parent_id, day, status) VALUES (3, 1, TRUE); INSERT INTO parents (id) VALUES (4); INSERT INTO children (parent_id, day, status) VALUES (4, 1, FALSE); INSERT INTO parents (id) VALUES (5); 

I need a request to be returned:

 Parents +------------+ | id | +------------+ | 1 | | 3 | +------------+ 

where id is the identifier of the parents. The summary table contains only parents who are always (any day) true . Please note that parents without children should be excluded.

My attempt:

 SELECT id FROM parents p INNER JOIN children c ON c.parent_id=p.id WHERE c.status = TRUE GROUP BY id 

But it will also provide id=2 to the parent element.

Another attempt:

 SELECT id FROM parents p LEFT OUTER JOIN children c ON c.parent_id=p.id AND c.status=FALSE WHERE c.status IS NULL GROUP BY id 

But this approach will also include a parent with id=5 , which should be excluded.

Answer 1

You do not need to join your parents.

 SELECT parent_id FROM children GROUP BY parent_id HAVING MIN(Status) = 'TRUE' AND MAX(Status) = 'TRUE' 

There is no status other than TRUE.

Answer 2

 SELECT id FROM parent P WHERE (P.id) IN (SELECT c.parent_id FROM children c WHERE c.status = TRUE) 

This will give you the desired result.

Answer 3

It may also work.

 SELECT DISTINCT p.id FROM parents p WHERE p.id IN ( SELECT c.parent_id FROM children c WHERE c.status = TRUE AND c.parent_id = p.id ) 

Answer 4

Use bit_add :

 select a.id from parents a join children b on a.id = b.parent_id group by a.id having bit_and(b.status); 

sqlfiddle