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

Dave Siddons wrote:

> 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
> >

Names not Darren but that is of little consequence.

Your question was a text-book example of a SQL problem ... by which I mean I've seen it in a textbook. So inspite of your protest to the contrary I've zero confidence in your statement about your background and "running it in production." In fact ... to take something you've just written today and run it in production tomorrow would be absolutely irresponsible. Any DBA that would let you do that needs more than a cup of coffee. So, yes, I am still convinced you are a student and that the story you've given Karsten is exactly that, a story.

The difference between the help given to students and those in the industry ... is that students don't learn anything if you just hand them the answer. Worse yet ... because most instructors grade on a curve ... your deceit actually hurts your fellow students and gives the instructor a false sense of how well instruction is being communicated.

Good thing you are not one of my students ... you'd be out of the program.

Want to convince me otherwise (not that I think you care or will) post back the modifications you claim you did to Karsten's work consistent with your description above. Then I'll be motivated to consider an apology.

Daniel Morgan Received on Wed Feb 05 2003 - 23:07:15 CST