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

From: Dave Siddons <davesiddons_at_lineone.net>
Date: Wed, 5 Feb 2003 17:50:45 -0000
Message-ID: <3e414ef6_3@mk-nntp-1.news.uk.worldonline.com>

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. Received on Wed Feb 05 2003 - 11:50:45 CST