Search code examples
mysqlsqldatabaseselectrdbms

How do I show the name of a user in my mapping table instead of his ID?

I have a User-Role Many-To-Many-Relationship in my MySQL database. Therefore I have a mapping-table which connects the IDs of both tables. The mapping-table looks like that:

ID of User | ID of Role
---------- | ----------
1          | 1
1          | 2
2          | 1
2          | 3

user-table:

Firstname of User | ID of User
----------------- | ----------
ExampleUser1      | 1
ExampleUser2      | 2

role-table:

Name of Role      | ID of Role
----------------- | ----------------
ExampleRole1      | 1
ExampleRole2      | 2
ExampleRole3      | 3

and I want my mapping-table to look like this:

Firstname of User | Name of Role
----------------- | ------------
ExampleUser1      | ExampleRole1
ExampleUser1      | ExampleRole2
ExampleUser2      | ExampleRole1
ExampleUser2      | ExampleRole3

What is the SQL-Query to realize that?

Solution

  • You can achieve this with joins

    select user.user_name, roles.role_name from mapping_table 
inner join user on user.id = mapping_table.user_id
inner join roles on role.id = mapping_table.role.id