SELECT statement HELP! 1

[sshelby1]

I am having trouble returning the correct records. My database structure is as follows:

TABLE1
ID

TABLE2
MEMBER_ID
START_YEAR
END_YEAR

START_YEAR and END_YEAR are stored in TABLE2 as a number, not an actual year.

Two numbers are static 36 and 55. I need to select any records that have a START_YEAR and END_YEAR range between 36 and 55.

Here is my current query but it only returns records with START_YEAR or END_YEAR values between 36 and 55. I would like to return any record that has a value between its START_YEAR and END_YEAR that falls between 36 and 55.

SELECT COUNT(ID) FROM TABLE1
INNER JOIN TABLE2 ON (MEMBER_ID = ID)
WHERE START_YEAR BETWEEN '36 AND '55' OR
END_YEAR BETWEEN '36' AND '55;

The following would not be returned in this quey but needs to be.

START_YEAR = 22
END_YEAR = 56

I hope this makes since. Thank you for any help!

 

[r937]

i have found that the neatest way to find overlapping ranges is to specify what you don't want

 where not
     ( START_YEAR < 36 and END_YEAR < 36 )
   and not  
     ( START_YEAR > 55 and END_YEAR > 55 )

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[sshelby1]

THANKS! It worked like a charm!

 

[kupe]

r937 - It is very neat. Thanks, Rudy, I'll borrow it too, and say cheers with a star. (Like your site.)