Mysql – thesql join member roles

MySQL

i need to get the members roles for specific users id

example return for the user id 4 the members roles
e.g id 4 peter father des brother

INSERT INTO `testdb`.`users` (`id`, `name`,`year`,`rank`) VALUES (4,"jef", 2006,1);
INSERT INTO `testdb`.`members` (`id`, `first_name`,`last_name`,`gender`) VALUES (1,"peter","zex",1);
INSERT INTO `testdb`.`members` (`id`, `first_name`,`last_name`,`gender`) VALUES (2,"des","zex",1);
INSERT INTO `testdb`.`member_roles` (`member_id`, `user_id`,`role`) VALUES (1,4,"father");
INSERT INTO `testdb`.`member_roles` (`member_id`, `user_id`,`role`) VALUES (2,4,"brother");

Best Answer

i need to get the members roles for specific users id

SELECT mem.`first_name`, mr.`role`
FROM member_roles mr
JOIN members mem
ON mr.`member_id`=mem.`id`
WHERE mr.`user_id` = 4

If you want the User info as well:

SELECT users.`name` AS "User Name", mem.`first_name` AS "Member First Name", mr.`role` AS "Member Role"
FROM member_roles mr
JOIN members mem
ON mr.`member_id`=mem.`id`
JOIN users 
ON mr.`user_id` = users.`id`
WHERE mr.`user_id` = 4