I have three tables and I need to merge them in a way that (table A LEFT JOIN table B) UNION (table A LEFT JOIN table C). I found out that VFP automatically sorted my data (but not the way I mentioned in my previous mail). For example,

TABLE A: A1 A2
TABLE B: (A1,B11,B21), (A1,B12,B21), (A2,'',B22)
TABLE C: (A1,C1), (A2,C1), (A2,C2)

RESULT: (A1,'',B22,''), (A1,B11,B21,''), (A2,B12,B21,''), (A1,'','',C1), (A2,'','',C1), (A2,'','',C2)

In other words, it sorts my data on the alphebetic order of the B1x, B2x, and Cx column. On the other hand, when I run the LEFT JOIN individually (i.e., without UNION them), the ordering is fine for each one. It is just when I UNION them, VFP sorts my data automatically and create the problem. There is no other key in the table that can identify the order except the recno(). Does anyone know how I can get the correct ordering in the view? Thanks.


Chu