Inner Joins and Left Joins can be done with the following syntax (for a right join, reverse the table order and use a left join):
SELECT * FROM {OJ Invoice INNER JOIN Customer ON (Invoice.CustomerRefListID = Customer.ListID)}
or:
SELECT * FROM Invoice INNER JOIN Customer ON Invoice.CustomerRefListID = Customer.ListID
Other hints: If you can include a starting value or range of values (> and <) for TxnDate or TimeModified as the first option in your WHERE statement you can improve your performance many times over.
Also there is a pretty big time penalty for using ORDER BY, if you can live without it you can improve performance. It is also faster to specify only the fields you require instead of a wildcard.
Note: Parentheses are required when there are more than two tables being joined:
SELECT COUNT(TxnID) FROM (Invoice INNER JOIN SalesRep ON Invoice.SalesRepRefListID = SalesRep.ListID) INNER JOIN Employee ON SalesRep.SalesRepEntityRefListID = Employee.ListID |