>>Hi,
>>
>>I'm doing a query which joins a customer table to a sales table (cab_inv) and for some reason the join doesn't want to pick up an index on customer number on the sales table (simple index on cust_num, index "cust_num2"), if I do a simple select only on the sales table and include in the where clause the customer number it picks up the index, now to make it more interesting if I go back to the join and I hard-code the customer number then the index is used. Any clues? image below and the optimizations, the first 4 lines is for the select on the sales table only and the index "cust_num2" kicking in, the rest if for the join and the index not kicking in.
>>
>>Thanks!
>
>What happens when you remove the b table from the query and only leave g and a?

Can't remove table 'b' because the join depends on it, I can remove table 'a' but it still issues with the index, I ended up switching the order between 'a' and 'b' and it's picking the index up, if 'a' goes before 'g' though then it doesn't pick up the index, has to go after 'g', the ordering matters, I know, confusing!

SELECT b.reg_num,b.ser_num, ;
b.appraisal,0000000.00 AS nDummy_Appr, ;
a.cust_num,a.Last_Name,a.First_Name,a.comp_name,b.event, ;
b.lot_num,b.price_paid ;
FROM CustHist b ;
LEFT JOIN Cab_Inv g ;
ON g.auct_num = b.event AND g.reg_num + g.ser_num = b.reg_num AND g.cust_num == a.cust_num ;
LEFT JOIN Customer a ;
ON a.cust_num = b.cust_num ;
WHERE STR(VAL(b.CUST_NUM),8)+DTOS(b.TRANS_DATE) = ' 9497'