>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

I am as confused as Cetin about what you are trying to solve. pk_person and pk_license are NOT primary key fields if they have duplicate values like 'P00000' or 'L00000'. I don't know why you would want a blank record in your person table. There is also no reason to have a license for a blank person in a join table that joins persons with licenses. A person with no license need not appear in the join table at all. A license issued to a business, not a person, also need not appear in a person-license join table.

I get the impression that the relationship of persons to businesses is the reason why it's hard to normalize this data. Since licenses can be issued to persons or businesses, a license report might list persons and businesses as if they are the same sort of thing. But I suppose that businesses are treated differently in other reports, and are kept in a different table with its own pk_business.

If businesses and persons are in separate tables, you might need one join table relating licenses to persons, and another one relating licenses to businesses. If you need a license report that lists both, you would use a UNION SELECT to stick them together.

Like Cetin, I'm a little unclear on what the data looks like right now. The "join table" you describe might be a reasonable cursor to make as a record source for some specific report, but is not a good table to make.