Need help with a join query

We have a requirement to get data from two tables in our database. The data from 2 tables looks as below:

Table 1

Emp No.	Start Date 1	         End Date 1
123	        31/01/2011 00:00:00	13/02/2011 00:00:00
123	        14/02/2011 00:00:00	14/02/2011 00:00:00
123	        15/02/2011 00:00:00	31/12/4712 00:00:00

Table 2
Emp No.	Start Date 2	         End Date 2	        Column 1
123	        21/07/2008 00:00:00	20/02/2011 00:00:00	AAAAA
123	        21/02/2011 00:00:00	31/12/4712 00:00:00	BBBBB

We need to get data from columns Start Date 1, End Date 1 & Column1 fields, where the End Date 1 lies between the Start Date 2 and End Date 2. So for example for the 3rd record in Table 1 it should pick the value BBBBB from column 1.
Also if there are 2 such values in Table 2 we should pick which has the max End Date 2.

I am having trouble with the query for this one. Any help would be appreciated. Thanks! Received on Tue Mar 08 2011 - 07:38:08 CST