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)
