Sub Query Problem Need help

[sahanas]

I have 2 tables joined by a Date field called say 'StartDate'.
Now this 'StartDate' should check against a 3rd table having 2 Date fields say 'FromDate' and 'ToDate' and see whether it falls between those 2 dates.
If 'Yes' then pick a column value from 3rd table and use that value as a divisor to some of the columns of the JOIN output of the first 2 tables.

I did the join of the first 2 tables and I tried to put a subquery for the 3rd table but did not get the resut.

What is the best approach?

 

[gradley]

Try using a join on the third table instead of a subquery:

JOIN Table3 
ON Table1.StartDate BETWEEN Table3.FromDate and Table3.ToDate

 

[ESquared]

Just be careful with BETWEEN because it is inclusive. Also, if your fromdate and todate have times attached as well as dates, you can end up missing records or selecting too many.

I think it's time for me to write a datetime FAQ!

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[SQLSister]

Excellent suggestion Esquared. I look forward to reading the FAQ.

Questions about posting. See faq183-874