Group-wise-max - hierarchical data query help

Question

Hi, the first part of this problem was solved by some good help on here yesterday, but I've been struggling today to complete the query I need. I am trying to pull multiple columns from 5 joined tables based on some conditions, but I want the result set to contain only one distinct "data entry" per p.id (the pet id which is a foreign key in the data entry table) - this data_entry should have the highest number of all the data_entries with that p.id (i.e a specific pet could have data_entries numbered 1,2 and 3 - I only want number 3). I have the code below working correctly in the first query, but I want to add an additional clause which checks the "updated" date of the max record returned, but I can't seem to work out how to integrate this clause correctly.

Any help would be greatly appreciated:

This simplified query works correctly across 3 joined tables (without the date comparison clause)

SELECT `p`.`id`, `o`.`id`, `o`.`email`, MAX(d.number) 
FROM (`pets` AS `p`, `owners` AS `o`, `data_entries` AS `d`) 
WHERE `p`.`owner_id` = `o`.`id` 
AND `p`.`id` = `d`.`pet_id` 
GROUP BY `p`.`id`, `o`.`id`, `o`.`email` 
ORDER BY `d`.`number` DESC 

However, when i try to add the date comparison clause below and the joins between the extra tables, the query doesn't perform the date comparison against the "updated" column of the data_entry with the MAX number, instead it checks against the the lowest number.

SELECT `p`.`id` AS `pet_id`, `o`.`id` AS `owner_id`, `o`.`email`, MAX(d.number) 
FROM (`pets` AS `p`, `owners` AS `o`, `data_entries` AS `d`, `k_records_owners` AS `kcro`, `k_records` AS `kcr`) 
WHERE `p`.`owner_id` = `o`.`id` 
AND `p`.`id` = `d`.`pet_id` 
AND `p`.`kc_number` = `kcr`.`do_dg_dog_no` 
AND `kcr`.`pa_breeder_no` = `kcro`.`contact_no` 
AND FROM_UNIXTIME(`d`.`updated`, "%Y-%m-%d") <= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 25 day), "%Y-%m-%d") 
GROUP BY `p`.`id`, `o`.`id`, `o`.`email` 
ORDER BY MAX(d.number) DESC

EDIT: latest attempt - results in Unknown column 'd2.updated' in 'having clause'

SELECT p.id AS pet_id, o.id AS owner_id, o.email, MAX(d.number) as max_d, d.updated 
FROM (pets AS p, owners AS o, data_entries AS d, data_entries AS d2, kennel_club_records_owners AS kcro, kennel_club_records AS kcr)
WHERE p.owner_id = o.id 
AND p.id = d.pet_id 
AND p.kc_number = kcr.do_dg_dog_no 
AND kcr.pa_breeder_no = kcro.contact_no 
AND d.number = d2.number
GROUP BY p.id, o.id, o.email 
having FROM_UNIXTIME(d2.updated, '%Y-%m-%d') <= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 25 day), '%Y-%m-%d') 
ORDER BY max_d DESC

Answer 1

try this:

SELECT p.id AS pet_id, o.id AS owner_id, o.email, MAX(d.number) as max_d, d.updated 
FROM (pets AS p, owners AS o, data_entries AS d, data_entries AS d2, kennel_club_records_owners AS kcro, kennel_club_records AS kcr)
WHERE p.owner_id = o.id 
AND p.id = d.pet_id 
AND p.kc_number = kcr.do_dg_dog_no 
AND kcr.pa_breeder_no = kcro.contact_no 
AND d2.number = max_d
AND FROM_UNIXTIME(d2.updated, '%Y-%m-%d') <= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 25 day), '%Y-%m-%d') 
GROUP BY p.id, o.id, o.email 
ORDER BY max_d DESC