linking on closest date HELP!!

[Domski74]

Hi there,

I have 2 tables, table A has a list with a primary key and dates, table B has a list containing a foreign key (to link to table A) with multiple entries for each key with different dates for each.

The problem is I need to link table A to table B on key and only pull records from table B where the date is closest (but not before) the date in table A, should say table B has other fields whose data I need returned but only for those that match as described.

I hope this isn't a confusing description, if you need clarification please post and I'll answer.

Many thanks for looking and I really hope you can help.

Domski

 

[ByteMyzer]

You might try something like the following (substitute highlighted text with actual table/field names):

SELECT [b][i]TableA[/i][/b].[b][i]priKey[/i][/b], [b][i]TableA[/i][/b].[b][i]MyDateField[/i][/b], [b][i]TableB[/b][/i].[b][i]forKey[/i][/b], [b][i]TableB[/b][/i].[b][i]MyDateField[/i][/b], ...

FROM [b][i]TableA[/i][/b]
INNER JOIN [b][i]TableB[/i][/b] ON [b][i]TableA[/i][/b].[b][i]priKey[/i][/b] = [b][i]TableB[/b][/i].[b][i]forKey[/i][/b]
WHERE [b][i]TableB[/b][/i].[b][i]MyDateField[/i][/b] =
   (SELECT MIN([b][i]TableB_2[/i][/b].[b][i]MyDateField[/i][/b])
    FROM [b][i]TableB[/i][/b] AS [b][i]TableB_2[/i][/b]
    WHERE [b][i]TableB_2[/i][/b].[b][i]forKey[/i][/b] = [b][i]TableA[/i][/b].[b][i]priKey[/i][/b]
    AND [b][i]TableB_2[/i][/b].[b][i]MyDateField[/i][/b] >= [b][i]TableA[/i][/b].[b][i]MyDateField[/i][/b]);

 

[Domski74]

Many thanks - I'll give it a go.