it is sort of possible!
Assuming your two order tables both contain a column called 'Order_number'
select *
from Order_Transaction AS OT
join Order_Purchased as OP
ON OT.Order_number = OP.Order_number
WHERE
OT.datePurchased >= @startDate AND OT.datePurchased <= @endDate
Use parameters (the @startDate and @endDate rather than concatenating strings, as doing string concatenation can lead to problems (imagine if someone typed "; drop table Order_Transaction" as one of the dates?)
You don't
have to use the join syntax - you could do
select *
from Order_Transaction AS OT,
Order_Purchased as OP
WHERE OT.Order_number = OP.Order_number
which is equally as valid, but tends to be less widely used