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

Karsten, thank you very much for your help. I have expanded on the advice you gave as the problem was a little more complex but that was the bit that I was confused with. I have tested this at home and it seems to work. Tomorrow I shall run the finished code in production (interested to see the run times as there are 2.5 million rows in each of four tables). Once again thank you very much for the help.

Darren, I understand your concern at helping students but surely as a lecturer you should stick a little closer to the ethics of teaching (that is of course ............. teaching). As I have already pointed out I am not a student. I am currently appointed as a technical leader. I have some 15 years development experience (none of this using SQL). I have 4 years experience as a DBA (Certified. But, as I am sure you will apreciate, that does not require deep SQL skills) in a DBA department that rarely gets involved with SQL development (tuning, yes, but this involves tuning already written code). I have followed many of your postings and to be honest you come across as an extremely rude and ignorant person. Don't assume that just because someone asks what you perceive to be a simple question that they are a student. If you find it both degrading to have to occasionally help less able (in a given area) professionals then I suggest you vacate the group completely. This group is here to help people not hinder them. Without the help of Karsten I may not have solved this problem by my deadline and consequently caused the customer severe problems.

Once again thank you very much Karsten .........

"DA Morgan" <damorgan_at_exesolutions.com> wrote in message news:3E41AC8D.A894205F_at_exesolutions.com...
> 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 - 19:37:08 CST