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
Received on Wed Feb 05 2003 - 15:42:59 CST
