>>>>I have PERSON and LICENSE. One person may have more than one license. One license may be issued to more than one person. Either the person or the license may be in both circumstances. That is, a person may be the only holder of one license and a shared holder of another license. Similarly, a license may be held by one person who may or may not hold another license, and those licenses may be single or shared as well.
>>>>
>>>>PERSON.pk_person is a primary key, and LICENSE.pk_license is primary
>>>>pk_person = 'P00000' and pk_license = 'L00000' are in each table for a 'blank' person or license.
>>>>
>>>>I would appreciate your guidance on an SQL-SELECT statement that will make a join table for this, selecting the pk from each table where there are matches, and supplying the 'blank' to make a match where there are none.
>>>>
>>>>That is, if a PERSON.Pnnnnn does not have a license, he is still in the join table as JOINED.fk_person = PERSON.Pnnnnn with license JOINED.fk_license = 'L00000' Similarly, if a license is issued, but not to a person (e.g. a business) the license is still in the join table 'Lnnnnn' with person 'P00000'
>>>>
>>>>Thanks for any help
>>>
>>>

---

Sounds like :
>>>

select * from Person ;
>>> full join License ;
>>>   on person.fk_person = license.pk_license

I got "person.fk_person" as foreign key linking to license (fk_person = "L00000" for pk_person = "P00000" and so on).
>>>Cetin
>>
>>Thanks, Cetin,
>>
>>I think I said something wrong. person.pk_person never equals license.pk_license
>>
>>As an example, suppose I have
>>
>>P0
>>P1
>>P2
>>P3
>>P4
>>P5
>>
>>and
>>
>>L0
>>L1
>>L2
>>L3
>>L4
>>L5
>>
>>I want to build
>> J0 P0 L0 - blanks join to satisfy RI
>> J1 P1 L1 - P1 has two licenses, this one is not shared
>> J2 P1 L2 - nor is this one
>> J3 P2 L3 - P2 shares one with P3
>> J4 P3 L3 - P3 shares with P2, and one of his own
>> J5 P3 L4 - P3 shares with P4
>> J6 P4 L4 - P4 share siwth P3
>> J7 P5 L0 - P5 has no license (so is L0)
>> J8 P0 L5 - L5 has no person (so is P0)
>>
>>The J values will be supplied by insert triggers when I append the result of this join onto the 'real' linker table
>>
>>Thanks again
>>
>>Al
>
>

---

Al,
>I'm afraid I'm confused. There is one-to-many from both sides. In order to provide it I think you have other intermediate tables. Or do you have some self recursion (as natively supported in SQL server - like employee_id and reports_to who is another employee) ?
>ie: How do you determine P3 has shares L3 and L4.
>Cetin

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