Hi,

I've been asked to do sql join strategies testing times to retrieve data.

I am doing a simple hash join of two tables
e.g.

Select /*ordered use_hash (customer) */ cust_lname
from customers, orders
Where customers.cust_id = <=500
AND customer.Order_id = orders.order_id

there are 2000 records in the customer table, and i expected 500 results....not the 1500 i got!
I would expect this large number of records if i was accessing the orders table (it has 3500 records with cust_id replication)...but not from just accessing the customer table!

Please please help, i'm so lost

:o)

But you ARE accessing the orders table. You have included it in the join and you are using it for selection criteria. If the object of this query is to only return customers who have orders AND whose customer id <=500, you can add the distinct keyword to get rid of the duplicate customer numbers caused by the join to the orders table. If your only criteria is customer id <=500, then remove all references to the orders table.

You should be aware that using old fashioned, non-specific joins causes Jet to create cartesian products. Use the newer syntax which specifies whether the join is Inner, Left, or Right.