Single table self-reference query with added complexity - how to

Transact-SQL
1

Full disclosure, I ran across this problem on a employment screening test and was unable to correctly solve it. Since then I have thought about it and while I have some ideas I can't make it work. I don't need the answer, just some direction.

There is a single table of people with id, name, age, motherId, and fatherId. The question was to list the parent's name and the age of their youngest child.

CREATE TABLE [dbo].[people](
[id] [int] NULL,
[name] varchar NULL,
[fatherId] [int] NULL,
[motherId] [int] NULL,
[Age] [int] NULL
)

In working on this from different angles I have decided that it will involve a UNION, since each child will be listed twice (potentially), once for the father, and once for the mother. So I need to work up a query that will list the child's age, and the child's mother's name and UNION that with child's age, and the child's father's name. Then there is the need to find only the youngest of siblings that share a parent, so a GROUP and a MIN perhaps.

I start with this:

SELECT p.Name, c.Age FROM People c, People p WHERE c.motherID = p.id

This gives me the mother's name and the age of all her children.

So

SELECT p.Name, c.Age FROM People c, People p WHERE c.motherID = p.id
UNION ALL
SELECT p.Name, c.Age FROM People c, People p WHERE c.fatherID = p.id

Gives me parent's name and the ages of all their children. Now to filter out all but the youngest for each parent.

Grouping on parent ID (for one side of the UNION) gives me the youngest for a parent:

SELECT MIN(c.Age) FROM People c, People p WHERE c.motherID = p.id GROUP BY c.MotherID

But I'm missing the parent;s name, and attempts to add in the result give an error. The query below is getting close, I have the mother's id and her youngest child's age:

SELECT c.motherID, MIN(c.Age) As MinAge FROM People c, People p WHERE c.motherID = p.id GROUP BY c.motherID

I just need to convert the id to a name, sounds easy, but at this point every thing I try just gives me ever more creative error messages.

So am I on the right track or do I need to go back to square one?

EDIT:

Found this on SO in response to the same question:

The SO answer lists both parents in a row, perhaps correct but not how I interrupted the question:

select m.name AS Mom,f.name AS Dad,min(y.age) AS Youngest
from people y
join people m on y.motherid=m.id
join people f on y.fatherid=f.id
group by m.name,f.name

Below I reworked my answer with the insight, it lists one parent per row as I was attempting to do:

select p.name AS Parent, min(c.age) AS Youngest from people c, people p WHERE c.motherid=p.id group by p.name
UNION ALL
select p.name AS Parent, min(c.age) AS Youngest from people c, people p WHERE c.fatherid=p.id group by p.name

Grouping on name instead of age, I'll have to digest that, what if two parents had the same name? Seems grouping should be on id or parent_id to be fully correct.

2

No usable sample data so I can't test it, but something like this I think would cover all the possibilities:

select p.name, p_min_age.age as age_of_youngest_child
from (
    select parentid, min(age) as age
    from dbo.people
    cross apply (
        values(fatherid),(motherid)
    ) as alias1(parentid)
    where parentid is not null
    group by parentid
) as p_min_age
inner join dbo.people p on p.id = p_min_age.parentid
order by p.name