Re: SQL to extract and evaluate rows from multiple child rows and 1 parent.

Karsten Farrell wrote:

> davesiddons_at_lineone.net said...
> > Hi
> >
> > Hope someone can help. My problem is this.
> >
> > I have two tables joined on ID. This is a one to many relationship. In the
> > child table the relevant columns are :
> >
> > id
> > status
> > start_date
> > end_date
> >
> >
> > I need to find each ID from the parent table where it has a child with a
> > status of 1 and a child with a status of 18 (doesn't matter if there are
> > other children.) and the end_date of the child with a status of 18 is
> > greater than the start_date of the child with a status of 1 and start_date
> > of the child with a status of 18 is less than the end_date of the child with
> > a status of 1.
> >
> > This needs to run fast so PL/SQL is not a good idea ( I have already written
> > this using PL/SQL)
> >
> > Does anyone know how this could be written in pure SQL (1 or more queries).
> >
> > I am a very inexperienced SQL coder so I maybe missing the obvious so just a
> > pointer in the right direction would help.
> >
> > Cheers ..... Dave Siddons.
> >
> Don't understand why you think PL/SQL can't be fast. But, that aside,
> are you looking for something like this (haven't put it thru sqlplus to
> check syntax or run it to check for correct functionality):
>
> select p.id
> from parent_table p
> , (
> select id,
> start_date,
> end_date
> from child_table
> where status = 1
> ) child_1
> , (
> select id,
> start_date,
> end_date
> from child_table
> where status = 18
> ) child_18
> where child_1.id = p.id
> and child_18.id = p.id
> and child_18.end_date > child_1.start_date
> and child_18.start_date < child_1.end_date;
>
> --
> /Karsten

As an instructor I appeal to you to please not do student's homework for them.

It isn't fair to the instructors, it isn't fair to the other students, and it isn't even fair to the person you are helping.

Thanks.

Daniel Morgan Received on Wed Feb 05 2003 - 18:30:05 CST