How to do a lower-than higher-than join

  • Thread starter Thread starter doyle60
  • Start date Start date
D

doyle60

I have a table of people and their birthdays.

I have another table with a DateLow and DateHigh and a tax bracket
number.

So if I have someone born on March 8, 1990, I want to match them up
with the tax number with the January 1, 1990 (DateLow) and June 27,
1990 (DateHigh). There are no overlapping dates in the table.

How do I do that in a query?

Thanks,

Matt
 
SELECT *
FROM tblTest INNER JOIN tblTestDates ON tblTest.TestDate >
tblTestDates.LowDate AND tblTest.TestDate < tblTestDates.HighDate

You have to create non-equi-joins like this in SQL view, the query design
grid doesn't support them.
 
Brendan,

Thanks, I didn't know about that either. In the past I've used unjoined
tables with Where clauses (designerable) i.e.,

SELECT tTestUser.UID, tTestUserCat.Tax
FROM tTestUser, tTestUserCat
WHERE (((tTestUser.DOB) Between [dtFrom] And [dtTo]));

I sampled both methods on 250K records, using a second query on the result
set to calculate average(Tax) and the above returned back in 7s, the INNER
JOIN method in 40-50s. Pure navigation was roughly equivalent, at 6 and 9 s.
This after compact between trials and changing order. But then I replaced
the > < in the INNER JOIN with a BETWEEN ... AND clause and returned back
much faster - essentially the same as the above. Didn't know either it was
such a big penalty without the BETWEEN! In future I'll stage bands as I
know it's inclusive and won't do >= to <.

Best Regards,
 
Back
Top