>>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