Hi Narong,

Since you by saying OR have given two alternative result sets, it seems to me you first of all would have to decide which of the alternatives you want...

As far as I can see from your example, the ID is the only joining condition between the tables, so using a normal join you would get a result like

ID NAME DIAG OPERATE NURSE
---------------------------------------------------------------------------------------------
1 Jim 001 A01 Jenny
1 Jim 002 A02 Jenny
1 Jim 001 A01 Anna
1 Jim 002 A02 Anna
1 Jim 001 A01 Mam
1 Jim 002 A02 Mam

You could do a SELECT TOP x ... (where x is the number of records in the largest table) to get the number of records you want, but without any ordering the content of the result is (in principle) arbitrary. By a normal join you would never arrive at the second result set from your example.

If you are in fact joining the tables by record number, matching the first record in Table 1 with the first record in Table 2, the second record in Table 1 with the second record in Table 2 and so on, you could arrive at the second result set, but never the first one. To do this, you could do it in code, by skipping etc. You could also do it like this:

SELECT *, RECNO() AS RecordNumber FROM Table1 INTO CURSOR Table1Cur
SELECT *, RECNO() AS RecordNumber FROM Table2 INTO CURSOR Table2Cur

SELECT Table1Cur.ID, Table1Cur.Name, Table1Cur.Diag, Table1Cur.Operate, Table2Cur.Nurse ;
FROM Table1Cur FULL OUTER JOIN Table2Cur ;
ON Table1Cur.RecordNumber = Table2Cur.RecordNumber ;
INTO CURSOR ResultCur

Hope this helps.

Arne


>Hi..All
>I have problem about Join table...
>I have 2 tabels for these
>Table 1 :
>-----------------------------------------------------------------------------------
>ID NAME DIAG OPERATE
>-----------------------------------------------------------------------------------
>1 Jim 001 A01
>1 Jim 002 A02
>-----------------------------------------------------------------------------------
>Table 2 :
>---------------------
>ID NURSE
>---------------------
>1 Jenny
>1 Anna
>1 Mam
>---------------------
>I want join 2 Table have maximum equal maximum of maximum table
>For this example ..maximum of join table = 3
>----------------------------------------------------------------------------------------------
>ID NAME DIAG OPERATE NURSE
>---------------------------------------------------------------------------------------------
>1 Jim 001 A01 Jenny
>1 Jim 002 A02 Anna
>1 Jim 001 A01 Mam
>----------------------------------------------------------------------------------------------
>OR
>----------------------------------------------------------------------------------------------
>ID NAME DIAG OPERATE NURSE
>---------------------------------------------------------------------------------------------
>1 Jim 001 A01 Jenny
>1 Jim 002 A02 Anna
>1 .NULL. .NULL. .NULL. Mam
>----------------------------------------------------------------------------------------------
>
>How I can do?
>
>Narong