>Cetin,
>
>I am sorry, I am confused too :) I am trying to straighten out some old data. I have a storage file that was apparently not normalized. It has person id and business id mixed in the same field (ssn and fed numbers) and license information for both types of licensee. along with the license number. Each record has several near duplicates relating to transactions for payment, apparently. I have extracted the persons that I can determine to be unique. Some records for the same person have different ssn or maybe the fed number!!
>but I got maybe 19k out of 25k person records. I have extracted the licenses that I can tell are unique. I know that every person in my person list has one license, and every license on my license list has one person. Each list also has an ssn that hopefully is correct, but is not unique.
>
>Where ssn matches I can assign my pk_person. Where license number matches I can assign my pk_license
>
>I want to use the outer join to help me build the linker table by selecting
>where A is in B, one or more join records, where B is in A one or more join records, where A is not in B a single record for A that is blank, where B is not in A a single record for B that is blank.
>
>select all the A's in B
>select all the B's in A
>one for each A not in B
>one for each B not in A
>into a table JoinTable
>
>then I can relate A into the JoinTable to see all of the B's assigned
>and vice versa to try to figure out which duplicates are valid and which are not.
>
>The storage table should be a linker, but it is not right. I am trying to make a linker out of what I know is right to reduce the number of records in the storage table that I haved to handle inidividually by inspection.
>
>I hope this explains better. Thank you for your patience.
>
>Al

---

Al,
I read this and Bret's messages. Things getting worse for me :) I can understand ssn but not fed number from point of business. To me it's just another piece of data.
As far as I understand there aren't tables like Person (where ssn is unique) and License (where fed_no unique) yet. Instead a table (lets call it "PerLic") with combined ssn+fed_no (ssn and fed_no parts have duplicates but not ssn+fed_no unless it's distinguished by another field such as transactiondate).
Well thinking RI as in its current style with DBC and primary keys was introduced with VFP it might be an acceptable approach. If repeated data for person and license are not so much then programmer might have chose the easy way of managing one table instead of parent-child 1-to-many.
If we call ssn+fed_no field as "SsnFed" (say it's 20 chars, 10 for each part) we could create Person and License :

select left(ssnfed,10) as pk_person, otherpersonspecificfields ;
from Perlic into table Person group by 1

select right(ssnfed,10) as pk_license, otherlicensespecificfields ;
from Perlic into table License group by 1

Here there is a cheat that accepts last entered table specific data for person or license as last updated&edited correct data for any given ssn or fed_no. Real implementation might be much harder in validating the correct data. Assuming this is OK for the moment, now we have a Person, License and Perlic. There is no direct relation between Person and License (even they don't have linker fields directly). PerLic serve as the tier and provide cross-one-to-many relation. I'll try to make correlation by using sample testdata.dbc.

Let's assume PerLic is Orders table (assume cust_id and emp_id fields are combined as cust_id+emp_id (each 10 chars) and we already done the splitting of tablespecific fields to Person (customer) and License (employee). To make the life easier we also splitted the "SsnFed" (cust_id+emp_id) to cust_id and emp_id fields). There is no direct relation between Customer and Employee but Orders tie them. An employee recorded many customer orders (License is shared by many person) and reverse a customer is served by more than one employee (a person has more than one license). Summarize :

PerLic -> Orders (don't care what primary is and ssnfed is split)
Person -> Customer (with cheat now cust_id is unique - pk)
License -> Employee (same as person)

No we can see the relationship as :
Customer -> Orders -> Employee
Or
Employee -> Orders -> Customer

If we "left join" the first 2 and "full join" the last (or make it "full join" in both to prevent confusion) we would get all :

select a.cust_id, ;
  b.cust_id as oCustId, b.emp_id as oEmpId, ;
  c.emp_id ;
  from customer a ;
  left join orders b ;
    on a.cust_id = b.cust_id ;
  full join employee c ;
    on b.emp_id = c.emp_id  ;
  order by 3,2

Hope this helps.
Cetin