MySQL SELECT MAX multiple tables : foreach parent return eldest son's picture
- by Guillermo
**Table parent**
parentId | name
**Table children**
childId | parentId | pictureId | age
**Table childrenPictures**
pictureId | imgUrl
no i would like to return all parent names with their eldest son's picture (only return parents that have children, and only consider children that have pictures)
so i thought of something like :
SELECT c.childId AS childId,
p.name AS parentName,
cp.imgUrl AS imgUrl,
MAX(c.age) AS age
FROM parent AS p
RIGHT JOIN children AS c ON (p.parentId = c.parentId)
RIGHT JOIN childrenPictures AS cp ON (c.pictureId = cp.pictureId))
GROUP BY p.name
This query will return each parent's eldest son's age, but the childId will not correspond to the eldest sons id, so the output does not show the right sons picture.
Well if anyone has a hint i'd appreciate very much
Thank you very much,
G
