I have a Parent with up to 6 Child Records.

I want to show all the child records in the Screen for the Parent Record using @SAYs.

I want to make a Self Join of my child tables so that all Child records with the same pl_account will be one record in the Cursor.

Unique key for this child table = pl_account + pl_letter

Table Osdpl:
pl_Account C 5
pl_letter C 1
pl_namelas C 20

Total Records = 89 records
pl_letter = 'A' = 56 records
pl_letter = 'B' = 23 records
pl_letter = 'C' = 9 records

A OR B = 79 records
A OR C = 65 records
B OR C = 32 records
A + B + C = 9 records

My goals:

56 records with no duplications of Names
These 56 will have

56 'A' names
23 'B' names
9 'C' names
That is, most of the records will have NULLs in the 'B' and 'C' portions


SELECT A.pl_account AS A_account, ;
A.pl_letter AS A_letter, ;
A.pl_namelas AS A_namelas, ;
B.pl_letter AS B_letter, ;
B.pl_namelas AS B_namelas, ;
C.pl_letter AS C_letter, ;
C.pl_namelas AS C_namelas ;
FROM Osdpl A, ;
Osdpl B, ;
Osdpl C ;
WHERE ;
A.pl_letter = 'A' ;
AND B.pl_letter = 'B' ;
AND C.pl_letter = 'C' ;
GROUP BY 1 ;
ORDER BY 1 INTO CURSOR Pat_list

There are 89 records.

The above join yields 56 records. This is the corrent number of unique pl_account records.

The 'A' columns are correct.

'B' columns are 90% the same A_namefir and A_namelas'.

'C' columns are also about 90% similar to each other -- C_namefir and C_namelas. But the names are completely different than those in the 'B' columns.