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

Dave Siddons wrote:

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

This is class homework and we do not do homework. We do, however, provide hints. Here are your hint.

1. This has nothing to do with PL/SQL so I'm quite confident you don't have a PL/SQL solution.
2. Use an inner join in the WHERE clause.
3. Create indexes based on your WHERE clause.
4. Run EXPLAIN PLAN to verify that you indexes are being used.

In the future don't post without mentioning version and edition (for example 9.2 Enterprise Edition), and if you have done something that is not working ... post your code.

Daniel Morgan Received on Wed Feb 05 2003 - 12:46:33 CST