>>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'
Since you're starting from the history file, you may not get customers without a history in your select result. BTW, you may want to use INNER JOIN with the Customers table as I assume customer is primary table and history is table related to the customer.
If it's not broken, fix it until it is.
My Blog