Re: Getting several columns from subselect with LIMIT 1

On Sat, Sep 20, 2008 at 11:36 AM, Pierre Thibaudeau <pierdeux(at)gmail(dot)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;