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

Filed under:
|
|
**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

Related posts about mysql

Related posts about select