April 21, 2004 at 10:43 am
I have been on a 3-year hiatus from SQL and I'm just coming back to it, trying to refresh my memory. I have a basic querying problem with two tables I have as follows:
Table1: date1, identifier1, data1
Table2: date2, identifier2, data2
The identifiers from these tables match exactly, which is perfect for my inner join. However, the dates do not. I would like to return all data from Table1 for which date1 falls in between successive dates of date2 for a given identifier.
I know I have to use a nested query for this, but I don't know how to go about doing it. Can anybody write back with how one would structure such a subquery?
Any help much appreciated!
-Paul
April 22, 2004 at 1:12 am
Select t1a.id1, t1a.date1 as fromdate,
t1b.date1 as uptodate, t2.date2 as innerdate
from Table1 T1A
inner join Table1 T1B
on t1b.id1 = t1a.id
and t1b.date1 > t1a.date1
and not exists ( Select 1
from Table1 T1C
where t1c.id1 = t1a.id
and t1c.date1 > t1a.date1
and t1c.date1 < t1b.date1 )
/* ensure T1B is the next, No T1C in between */
inner join Table2 T2
on t2.id2 = t1a.id1
and t2.date2 between t1a.date1 and t1b.date1
and t2.date2 < t1b.date1
I might have swapped the roles of table1 & table2.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply