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). However, the result I got is kind of in reverse order like the following.

TABLE A: A1 A2
TABLE B: (A1,B1), (A1,B2), (A2,B1)
TABLE C: (A1,C1), (A2,C1), (A2,C2)

RESULT: (A1,B2,''), (A1,B1,''), (A2,B1,''), (A1,'',C1), (A2,'',C2), (A2,'',C1)

When I run the LEFT JOIN individually, the ordering is fine, but once I UNION them, the order reverses. 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