List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:May 7 1999 10:27am
Subject:Re: In place of Nested SELECTs
View as plain text  
Alan Zaitchik wrote:
> 
> 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

Hi Alan

How about a self JOIN?

SELECT
	p.*
	, c.*
	, g.*
FROM
	person p
	, person c
	, person g
WHERE
	(p.child1ssn=c.ssn
	OR p.child2ssn=c.ssn
	OR p.child3ssn=c.ssn
	OR p.child4ssn=c.ssn
	OR p.child5ssn=c.ssn
	) AND
	(c.child1ssn=g.ssn
	OR c.child2ssn=g.ssn
	OR c.child3ssn=g.ssn
	OR c.child4ssn=g.ssn
	OR c.child5ssn=g.ssn
	)

This would not deliver all infos for one person in one row, but you can get all stages
(person,children,and grandchildren).
If you need all persons with and without children/grandchildren, then you have to change
the JOIN to a LEFT JOIN in the FROM clause.

Tschau
Christian

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