Assuming that all tables have the columns referenced in your query, and IDs have the meaning implied by their names, I think this should be a working query.
select users.username, groups.name
from users
inner join roles on roles.userid=users.userid
inner join groups on roles.groupid=groups.groupid
If you get an exception, could you post more lines from the stack trace?