CODEWITHSUNDEEP
phpmysql
D_R
D_R

Reputation: 4962

Join multiple records to one row?

This is my DB Structure:

Users {User ID 1}
Roles {For example 2 roles: 1, 2}
User_roles {User ID 1: has Role 1, User ID 1: Has role 2}

How can I use join to get both of the roles since this Query duplicate the user:

SELECT * FROM `users` LEFT JOIN `user_roles` ON (`users`.`id` = `user_roles`.`user_id`)

And I want to output a table that will look like that:

User ID | Has Role 1 | Has Role 2
--------|------------|-----------
1       |    yes     |    yes

Upvotes: 2

Views: 69

Answers (2)

xdazz
xdazz

Reputation: 160833

You could group by user's id.

SELECT 
    `users`.`id`, 
     IF(SUM(`roles`.`id` = 1), 'yes', 'no') AS `Has Role 1`,
     IF(SUM(`roles`.`id` = 2), 'yes', 'no') AS `Has Role 2`
FROM `users` 
LEFT JOIN `user_roles` ON `users`.`id` = `user_roles`.`user_id`
LEFT JOIN `roles` ON `roles`.role_id = `user_roles`.`role_id`
GROUP BY `users`.`id`

And if you just use the role's pk, then you may omit LEFT JOIN roles by

SELECT 
    `users`.`id`, 
     IF(SUM(`user_roles`.`role_id` = 1), 'yes', 'no') AS `Has Role 1`,
     IF(SUM(`user_roles`.`role_id` = 2), 'yes', 'no') AS `Has Role 2`
FROM `users` 
LEFT JOIN `user_roles` ON `users`.`id` = `user_roles`.`user_id`
GROUP BY `users`.`id`

Upvotes: 3

vandango
vandango

Reputation: 567

Try this

SELECT
    usr.UserId,
    CASE WHEN role.HasRole1 = 1 THEN 'Yes' ELSE 'No' END AS HasRole1,
    CASE WHEN role.HasRole2 = 1 THEN 'Yes' ELSE 'No' END AS HasRole2
FROM users AS usr
LEFT JOIN user_roles AS role ON usr.id = role.user_id

Upvotes: 2

Related Questions