CODEWITHSUNDEEP
mysqlsql
nickbusted
nickbusted

Reputation: 1109

Select rows where field in joining table is same in every row

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 query that will return:

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

where id is parents id. The resulting table only contains the parents that always(any day) true. 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 give parent with id=2.

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 parent with id=5, which must be excluded.

Upvotes: 5

Views: 225

Answers (4)

rushisangani
rushisangani

Reputation: 3385

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.

Upvotes: 1

Fabricator
Fabricator

Reputation: 12772

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

Upvotes: 0

vmachan
vmachan

Reputation: 1682

This might 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 
               )

Upvotes: 0

dnoeth
dnoeth

Reputation: 60462

You don't need to join to parents.

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

No other Status besides TRUE.

Upvotes: 8

Related Questions