foreach parent return eldest son's picture

[guillermobytes]

hello i have 3 tables : parent, children, childrenPictures

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 p.name AS parentName,
  cp.imgUrl AS imgUrl
FROM parent AS p
   RIGHT JOIN children AS c ON (p.parentId = c.parentId)
   RIGHT JOIN childrenPictures AS cp ON (c.pictureId = cp.pictureId))
 WHERE c.age IN (SELECT c.age AS age FROM children AS c GROUP BY c.parentId ORDER BY c.age DEST LIMIT 0,1)

I would like to join the subquery but don't know how to do that, anywaysI can't figure out how to solve this...

Well if anyone has a hint i'd appreciate very much

Thank you very much,

guillermobytes

[guillermobytes]

Ok so i finally wrapped my head around it!

The query would be :

SELECT 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

The secret was to put a max() coupled with a group by.
Every child that has the same parent is put into a group/set (GROUP BY parent name) as GROUP BY returns only one row per distinct value in column, the max() function tells which child in the group must be returned.
Well that is how i interpreted it... lol

Regards

[Rabbit]

Does that work? I haven't tried it but it doesn't look like it would work.

Don't you have to use a subquery to get it to work?

[guillermobytes]

You are right rabbit, it doesn't work!! :S
So if any one has a hint I'm still interested!

[Rabbit]

You'll need to use a subquery, to return the correct picture based on the child ID.

[guillermobytes]

what i can't figure out how to do, is to get the correct child id, so far what i can get is the correct max age and the parent name.

[guillermobytes]

Well i finally found the solution

SELECT mx.maxAge AS maxAge, 
    cc.childrenCount AS childrenCount, 
    i.localUrl AS imageLocalUrl, 
    p.name AS parentName
FROM (SELECT parentId, 
            MAX(age) AS maxAge
        FROM Children 
        GROUP BY parentId) AS mx
INNER JOIN (SELECT parentId,
            COUNT(*) AS childrenCount 
           FROM children 
            GROUP BY parentId) AS cc ON (mx.parentId = cc.parentId)  
    INNER JOIN children AS c ON (mx.parentId = c.parentId) 
    INNER JOIN parent AS p ON (mx.parentId = p.parentId) 
    INNER JOIN children_image AS i ON (c.imageId = i.imageId) 
    WHERE c.maxAge = mx.maxAge 
    GROUP BY c.parentId