Am I doing something out of the ordinary here? I thought this was a pretty basic query.
>On second thought I doubt that will work faster. Again it compares a record from table1 with ALL records from Table2.
>Let me think...
>
>>I'll try... thanks!
>>
>>>Hmmm,
>>>How about:
>>>
>>>DECLARE @Temp TABLE (table1ID int, Table2Id, StartDate Datetime, EndDate DateTime)
>>>INSERT INTO @Temp
>>>SELECT Table1.Id,
>>> Table2.Id,
>>> Table2.StartDate,
>>> Table2.EndDate
>>>FROM Table1
>>>FULL JOIN Table2 ON 1=1
>>>
>>>
>>>SELECT .....
>>> FROM Table1
>>> LEFT JOIN @Temp Temp ON Table1.Id = Temp.Id AND Table1.EffectiveDate BETWEEN Temp.StartDate AND Temp.EndDate
>>>
>>>I am not sure if this would speed something.
>>>
>>>
>>>>Table1 contains a record for each day that contains certain interest rates.
>>>>Table2 contains a record for a range of days (specified by StartDate and EndDate - no overlap) that contains other interest rates.
>>>>
>>>>I want to link the tables, and view the corresponding Table2 record with each Table1 record. The tables are unrelated otherwise.