how to combine 2 or more bridge tables in a single query

Question

i have the following tables:

Table: People (id, first, last, age, phone, etc . .)
Table: Roles (id, name)
Table: Skills (id, name)
Table: People_Roles (id, personID^, roleID^)
Table: People_Skills (id, personID^, skillID^)

^ = foreign key

I basically want a query that gives me the full result set of all people and their roles and there skills.

Person.First, Person.Last, Roles.Name, Skills.Name

Answer 1

SELECT p.first, p.last, r.name, s.name
FROM People p
LEFT JOIN People_Roles pr
ON pr.personID = p.id
INNER JOIN Roles r
ON pr.roleID = r.id
LEFT JOIN People_Skills ps
ON ps.personID = p.id
INNER JOIN Skills s
ON ps.skillID = s.id

This query will select you all the people, even those without Roles or Skills assigned.

Answer 2

This query will work:

SELECT Person.First, Person.Last, Roles.Name, Skills.Name  
FROM People 
 LEFT JOIN People_Skills 
  ON People.id = People_Skills.personID
 INNER JOIN Skills
  ON People_Skills.skillID = Skills.id
 LEFT JOIN People_Roles 
  ON People.id = People_Roles.personID
 INNER JOIN Roles
  ON People_Roles.roleID = Skills.id

By the way, why your "bridge" tables have their own ID. I would just use a compound key made of PersonID and the corresponding foreign key (skillsID or rolesID) to ensure that every pair can only be made once.