Обсуждение: Getting several columns from subselect with LIMIT 1

I have a PERSONS table.
I also have a PROGENY table, which is a many-to-many association table
with two foreign keys to the PERSONS table to itself.
(In this day and age, not only can an individual have any number of
children, but also a person can have any number of parents!  At least,
let's assume that's true for the sake of this setup.)

Suppose I wish to construct a view of the persons, along with the name
of their first-born (if they have one;  NULL otherwise).  The
following SELECT does just that:

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.

Any suggestion?

Pierre Thibaudeau wrote:
> I have a PERSONS table.
> I also have a PROGENY table, which is a many-to-many association table
> with two foreign keys to the PERSONS table to itself.
> (In this day and age, not only can an individual have any number of
> children, but also a person can have any number of parents!  At least,
> let's assume that's true for the sake of this setup.)
>
> Suppose I wish to construct a view of the persons, along with the name
> of their first-born (if they have one;  NULL otherwise).  The
> following SELECT does just that:
>
> 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.
>
> Any suggestion?
>
>
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)

best regards,
Marcus

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)

On Sat, Sep 20, 2008 at 11:36 AM, Pierre Thibaudeau <pierdeux@gmail.com> wrote:
> I have a PERSONS table.
> I also have a PROGENY table, which is a many-to-many association table
> with two foreign keys to the PERSONS table to itself.
> (In this day and age, not only can an individual have any number of
> children, but also a person can have any number of parents!  At least,
> let's assume that's true for the sake of this setup.)
>
> Suppose I wish to construct a view of the persons, along with the name
> of their first-born (if they have one;  NULL otherwise).  The
> following SELECT does just that:
>
> 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.



SELECT (person).*, (progeny).* from
(
   select persons as person,
       (
               SELECT progeny
               FROM progeny JOIN persons child ON child.id = progeny.child
               WHERE progeny.parent = persons.id
               ORDER BY child.birthdate ASC
               LIMIT 1
       ) AS firstborn
  FROM persons;
) q;