Fetch parent table rows whose related children ALL match a given criteria?

Question

I have two tables with date_created columns and a foreign key relation:

CREATE TABLE `parent` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_created` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `child` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `date_created` datetime NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
) ENGINE=InnoDB;

I need to fetch only the parents whose children all have a date_created of less than January of this year.

My first thought was to use the distinct parent_id from all children matching this criteria:

select distinct parent_id
from child
where date_created < '2015-01-01'

Problem is, this also returns parents with children outside the criteria:

select id, date_created
from child
where parent_id in (
  select distinct parent_id
  from child
  where date_created < '2015-01-01'
) and date_created >= '2015-01-01'
-- 22 rows returned

Um, maybe you just need a large cup of coffee. – Strawberry Feb 09 '15 at 16:23 — Strawberry, Feb 09 '15 at 16:23

score 1 · Accepted Answer · edited May 23 '17 at 12:16

What I did is selected all parents who have children created before 2015-01-01, and no parents that have children created after 2015-01-01.

I removed the last line of your WHERE clause that read and date_created >= '2015-01-01'.

Here is the query:

select p1.id, p1.date_created
from parent p1 
where exists
(
  select 1 
  from child c1
  where 1=1
  And c1.parentId = p1.id
  And c1.date_created < '2015-01-01'
)
And Not Exists
(
  select 1 
  from child c2
  where 1=1
  And c2.parentId = p1.id
  And c2.date_created > '2015-01-01'
)

Also, I think you should use either a JOIN or an EXISTS instead of IN or NOT IN. Here is an interesting thread on this topic:

NOT IN vs NOT EXISTS

Fetch parent table rows whose related children ALL match a given criteria?

1 Answers1