--- On Wed, 2/23/11, Wade Preston Shearer <[email protected]> wrote:
> I am having difficulty constructing an MySQL query. I have a
> table of parents and another of children. The parents table
> contains a column for an optional favorite child assignment
> (child primary key). I need to retrieve a list of parents and
> a single child. If the parent has a favorite child assignment,
> then the child should be the favorite child. If the parent
> doesn't have a favorite child assignment, then the child should
> be the youngest child.
> Is this possible in a single query?
Maybe. I haven't tested this, so it's probably not perfect
(especially getting the youngest child), but the general idea
would probably go like this:
SELECT * FROM (
(SELECT *
FROM
parent p
INNER JOIN child c ON c.ChildID = p.FavoriteChildID)
UNION
(SELECT *
FROM
parent p
LEFT OUTER JOIN child c ON c.ParentID = p.ParentID
WHERE
p.FavoriteChildID IS NULL AND
c.Age = MAX(c.Age)
GROUP BY p.ParentID
)
) a;
_______________________________________________
UPHPU mailing list
[email protected]
http://uphpu.org/mailman/listinfo/uphpu
IRC: #uphpu on irc.freenode.net
