Getting several columns from subselect with LIMIT 1

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>:

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;