MySql Join many to one but get only one related record based on a condition

Question

I would like to join on a many to one relationship, but pick one of the records to join based a condition, in this case the maximum date. Here is a simplified schema;

parent
---------
id | name
---------
 1 | Bob
 2 | Mary
 3 | Pat

child
----------------------------------
id | parent_id | name | birthdate
----------------------------------
11 | 1         | Anne | 2014-01-01
12 | 2         | Jane | 2014-01-02
13 | 3         | John | 2016-06-01
14 | 1         | Mark | 2016-08-01
15 | 2         | Jack | 2016-01-01
16 | 2         | Jill | 2016-01-01

A parent can have zero or more children. A child's birthdate can be in the future (a pregnancy). Two children from the same parent can have the same birthdate (twins).

I would like to get the parent record and their youngest child. In the case of youngest twins, I don't care which child is chosen. So in the this case, Bob has two children, their youngest is Mark who is due to be born on August 8. Mary has 3 children. Her two youngest are twins, Jack and Jill. Pat has no children. So I would like to get;

id | name | child | birthdate
-----------------------------
 1 | Bob  | Mark  | 2016-08-01
 2 | Mary | Jack  | 2016-01-01
 3 | Pat  | null  | null

Is it possible to do this in a single query?

Similar question that might be useful: http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results . With parent = group from the question — drneel, Feb 04 '16 at 20:17
I think there is an error in your data: parent with id = 3 has one child, `'John'`. — Giorgos Betsos, Feb 04 '16 at 20:28
And Jane is younger than Jack for the parent Mary. I think you have to correct your input or output accordingly — Slowcoder, Feb 04 '16 at 20:40

score 2 · Accepted Answer · answered Feb 04 '16 at 20:44

SELECT p.id, p.name, c.name, birthdate FROM parent p
LEFT JOIN child c ON p.id = c.parent_id AND c.birthdate <= NOW()
WHERE c.birthdate IS NULL OR birthdate = (SELECT MIN(birthdate) FROM child c2 WHERE c2.parent_id = p.id)
GROUP BY p.id

SQL Fiddle here. http://sqlfiddle.com/#!9/a5fec2/2

NOTE: Input data has been modified little bit to get your desired output.

score 1 · Answer 2 · answered Feb 04 '16 at 20:25

You can use the following query:

SELECT p.id, p.name, c.name, c.birthdate
FROM parent AS p
LEFT JOIN (
  SELECT parent_id, name, birthdate,
         @rn := IF(@pid = parent_id, @rn + 1,
                   IF(@pid := parent_id, 1, 1)) AS rn
  FROM child
  CROSS JOIN (SELECT @rn := 0, @pid := 0) AS vars
  ORDER BY parent_id, birthdate DESC
) AS c ON p.id = c.parent_id AND c.rn = 1

Variables are used in the derived table, so as to get the latest child per parent.

Demo here

score 1 · Answer 3 · answered Feb 04 '16 at 21:02

1

SELECT 
  `p`.`id`,
  `p`.`name`,
  `c`.`name` AS `child`,
  `c`.`birthday`
FROM `parent` AS `p`
LEFT JOIN `child` AS `c`
ON `p`.`id` = `c`.`parent_id`
GROUP BY `c`.`parent_id`
ORDER BY `p`.`id`;

http://sqlfiddle.com/#!9/b70acf/2

answered Feb 04 '16 at 21:02

RhinoLarva

735
10
16

MySql Join many to one but get only one related record based on a condition

3 Answers3