Select username and role name

Question

As the title indicates I'm trying to select both username and role name by using the following query.

select u.username, r.name
from users u, role r
inner join users_roles ur
  on ur.user_id = u.id
where username = ?;

However I'm getting the below error

[2017-04-05 21:34:49] [42P01] ERROR: invalid reference to FROM-clause entry for table "u"
[2017-04-05 21:34:49] Hint: There is an entry for table "u", but it cannot be referenced from this part of the query.
[2017-04-05 21:34:49] Position: 79

My user entity is as follows

@Entity(name = "users") // Postgres doesn't like the table name "user"
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;
    private String username;
    ...

    @ManyToMany
    @JoinTable(
            name = "users_roles",
            joinColumns = @JoinColumn(
                    name = "user_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(
                    name = "role_id", referencedColumnName = "id"))
    private Collection<Role> roles;
...

And my role entity is as follows

@Entity
public class Role {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;
    private String name;
    @ManyToMany(mappedBy = "roles")
    private Collection<User> users;
...

Any clues about what I'm doing wrong?

Answer 1

SELECT users.username, role.name
FROM users
LEFT OUTER JOIN users_roles
  ON users.id = users_roles.user_id
LEFT OUTER JOIN role
  ON users_roles.role_id = role.id
WHERE username = ?