I want to display the record number of my customer table from SELECT-SQL. I am able to do this when I use only the customer table, but if I join another table, I am unable to display the record number from the customer table.

If I issue: "SELECT RECNO(), fname, lname FROM Customer INTO CURSOR lcTest," I get Customer.dbf's record number for each record.

If I now join a second table into the SELECT-SQL, i.e., If I issue: "SELECT RECNO(), a.fname, a.lname, b.invoice FROM Customer a, Invoice b WHERE a.num = b.num INTO CURSOR lcTest," I get a record number, but it is not the record number from the Customer table, which is what I want.

Hi Terry :

Try the following sintax and see :

Involving 2 tables :

Select ;
RecNo(), T1.FIELD1, .., T1.FIELDN, T2.FIELD1, .., T2.FIELDN ;
From T1 ;
Left Join T2 On T2.KEY = T1.KEY

Involving 3 tables :

Select ;
Recno(), T1.FIELD1, .., T1.FIELDN, T2.FIELD2, .., T2.FIELDN, ;
T3.FIELD3, .., T3.FIELDN ;
From T1 ;
Left Join T3 On T3.KEY = T2.KEY ;
Left Join T2 On T2.KEY = T1.KEY

This works perfectly, but you must pay attention in the order shown to get this working, ok.


"..Whatever the route, it eventually returns to him.. "