Redmine

I need to create a report of a list of users and the groups they are in. I'm having a hard time getting my join statement to work because the group names are also in the users table.

At the moment I have something like this

SELECT `id` , `login` , `firstname` , `lastname` , `mail` , `status` FROM `users` LEFT JOIN `groups_users` ON `users`.`id`=`groups_users`.`user_id`

Which isn't the result I wanted. I'd like to have a new column that shows the group they are in next to each row in users.