Join Tables that don't have exact matches

I have a table that has a Sales Tax Code and an Invoice Date (ex. 7/04/07)
and another table that has the Sales Tax Code, Tax Rate, and Effective Date.
The second table can have the same Sales Tax Code with a different Tax Rate
and Effective Date. I want to select only one Tax Rate record from the
second table and that is the one that has the greatest effective date that is
also less than the invoice date.

I have tried the Top 1 method in a join and where statement, but it is not
actually limiting the records from the second table.

can you post some sample records and expected result set out of it.

--
Vishal Parkar
vgparkar.TakeThisOut@yahoo.co.in



"Queryless in SLC" wrote:

> I have a table that has a Sales Tax Code and an Invoice Date (ex. 7/04/07)
> and another table that has the Sales Tax Code, Tax Rate, and Effective Date.
> The second table can have the same Sales Tax Code with a different Tax Rate
> and Effective Date. I want to select only one Tax Rate record from the
> second table and that is the one that has the greatest effective date that is
> also less than the invoice date.
>
> I have tried the Top 1 method in a join and where statement, but it is not
> actually limiting the records from the second table.