List:General Discussion« Previous MessageNext Message »
From:Alan Zaitchik Date:May 6 1999 6:49pm
Subject:In place of Nested SELECTs
View as plain text  
Can someone tell me the best way to do the following?
I have a table, call it PERSON, with an index on SSN (char 9) and
various data fields. Five of these fields are SSN values of "children",
so that if you wanted to get a person's grandchildren you would want to
do something like
    SELECT *  FROM PERSON WHERE SSN in {
    SELECT child1ssn, chidl2ssn, child3ssn,child4ssn,child5ssn FROM
PERSON WHERE SSN in{
     SELECT child1ssn, chidl2ssn, child3ssn,child4ssn,child5ssn FROM
PERSON WHERE SSN = some_passed_value.}}}
Actually I want the data for the person with SSN = some_passed_value as
well as the children, not just the grandchildren, but I didn't want to
complicate the example. And in atual fact the table is not for people
and offspring, but that doesn't matter here. (That's why it is ok for me
to have a small fixed number of "childssn" fields.)
I am accessing the database through jdbc so I could traverse the result
set from the innermost query and do the others, but that seems
inefficient. What is the best way to do a nested SELECT of this sort
through other means?
Thanks!
Alan Zaitchik


Thread
In place of Nested SELECTsAlan Zaitchik6 May
  • Re: In place of Nested SELECTsFred Read7 May
    • Re: In place of Nested SELECTsMartin Ramsch7 May
  • Re: In place of Nested SELECTsChristian Mack7 May
    • seminars and conferecesA. Brandic7 May
      • seminars and conferecesAntti S Halonen8 May