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