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@xxxxxxxxx>: >> >> 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)
