SQL query to join users and groups_users table
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.