An example of how to create a table relating a user table and a role table and an example of how to join across the two.

User Role Example.sql

CREATE TABLE user_role (
  user_id INT(10) UNSIGNED NOT NULL,
  role_id INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (user_id, role_id),
  CONSTRAINT user_role_role_fk FOREIGN KEY (role_id) REFERENCES role (id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT user_role_user_fk FOREIGN KEY (user_id) REFERENCES `user` (id)
    ON DELETE CASCADE ON UPDATE CASCADE
);

SELECT * FROM `user` u
LEFT JOIN user_role ur ON u.id = ur.user_id
LEFT JOIN role r ON ur.role_id = r.id
WHERE u.username = 'eterkki';