Ever needed to get all projects associated with every user in redmine? Here’s a query that will help
Multiple Joins sql query; To get user, all projects associated with user and role on that project.
Exmaple:
|
Table |
Column Names |
|
users |
id, firstname, lastname, login |
|
roles |
role_id, name |
|
member_roles |
member _id, role_ id |
|
members |
user_id, project_ id |
select users.firstname, users.lastname, users.login, projects.name, roles.name from members
inner JOIN member_roles
ON member_roles.member_id=members.id
inner Join users
ON users.id=user_id
inner Join projects
ON projects.id=project_id
inner Join roles
ON roles.id=member_roles.role_id
order by user_id ASC
redmine all users projects query
Multiple Joins sql query; To get user, all projects associated with user and role on that project.