MySQL SELECT MAX multiple tables : foreach parent return eldest son's picture
Posted
by
Guillermo
on Stack Overflow
See other posts from Stack Overflow
or by Guillermo
Published on 2011-01-16T01:44:57Z
Indexed on
2011/01/16
1:54 UTC
Read the original article
Hit count: 590
**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
© Stack Overflow or respective owner