Fwd: Getting several columns from subselect with LIMIT 1

Thanks for the suggestion.  Unfortunately I get an INVALID COLUMN
REFERENCE (SQL state: 42P10)
to the effect that the subselect in the FROM clause cannot reference
other tables at the same request level.


2008/9/20 Marcus Engene <mengpg2@engene.se>:
>>
>> SELECT
>>        persons.*,
>>        (
>>                SELECT child.name
>>                FROM progeny JOIN persons child ON child.id = progeny.child
>>                WHERE progeny.parent = persons.id
>>                ORDER BY child.birthdate ASC
>>                LIMIT 1
>>        ) AS firstborn_name
>>  FROM persons;
>>
>> Now, this is probably not the most elegant piece of code, but the real
>> problem is that
>> I cannot see how to extend it to the case where I want not only the
>> firstborn's name but also the firstborn's ID
>> (short of repeating the entire subselect a second time).  At the
>> moment, with this current syntax, my subSELECT statement would not be
>> allowed to return more than a single column.
>
> Would this work?
>
> select
>   p.*
>  ,pp.*
> from
>   persons p
>  ,(
>       SELECT child.name, child.id
>       FROM progeny JOIN persons child ON child.id = progeny.child
>       WHERE progeny.parent = p.id
>       ORDER BY child.birthdate ASC
>       LIMIT 1
>   ) as kid(kid_name,kid_id)