SQL Many to Many Relationships

For a many to many relationship it requires three tables. In this example we will use the Users, Roles, and UserRoles tables. The UserRoles table is the table that holds keys from the other two tables.

To get all of the users with a RoleId of 3 you would execute the following SQL statement:

SELECT u.*
FROM Users AS u
INNER JOIN UserRoles AS r
ON u.User_ID = r.UserId
WHERE r.RoleId = 3

To get all of the roles for a user with a UserId of 12 you would execute the following SQL statement:

SELECT r.Company, r.Division, r.Department, r.JobRole AS "Job Role"
FROM Roles AS r
INNER JOIN UserRoles AS ur
ON ur.RoleId = r.RoleId
WHERE ur.UserId = 12