The first table, PeopleToRole, lists primary keys for people and primary keys for their roles. One person can have multiple roles and thus multiple rows. PK_People,PK_Role.

The second table, PeopleToSection, lists primary keys for people and primary keys for their assigned sections. One person can have multiple sections and thus multiple rows. PK_People,PK_Section.

A person’s roles vary by ‘section’. The third table, PeopleRoleSection, lists ranks that people have in particular sections. PK_People,PK_Role,PK_Section.

My task is to take everyone that has a certain two roles in PeopleToRoles (ex: PK_Role in (1,2)), and insert those two rows into PeopleRoleSection for every section they belong to according to PeopleToSection.

I’ve been running in circles trying to figure this out without writing a giant messy clump of insert statements. Any help would be appreciated!

>Solution :

Perhaps I’m misunderstanding (Sample data and desired results would help a lot for our understanding), but joining PersonToRole and PersontoSection on PK_Person and limiting for the two roles should do the trick, I believe:

INSERT INTO PeopleRoleSection (PK_Person, PK_Role, PK_Section)
SELECT ptr.PK_Person, ptr.PK_Role, pts.PK_Section 
FROM PeopleToRole ptr 
    INNER JOIN PeopleToSection pts 
        ON ptr.PK_Person = pts.PK_Person 
WHERE ptr.PK_Role IN (1,2);

Obviously, run that SELECT by itself before letting it loose as an INSERT query.