Include admin role in users table from roles table

Question

Is there a way to query users table like this:

| id | username |
-----------------
| 1  | user1    |
| 2  | user2    |
| 3  | user3    |

and user_roles table:

| id_user | id_role |
---------------------
|    1    |    1    |
|    1    |    2    |
|    1    |    3    |
|    2    |    2    |
|    3    |    1    |

assuming that role with id = 1 is an admin role, to produce result that looks like this:

| id | username | admin |
-------------------------
| 1  | user1    |   Y   |
| 2  | user2    |   N   |
| 3  | user3    |   Y   |

I think it can be done using nested SELECT statements, but I was wondering if it's doable using JOIN.

Edit: The admin column value doesn't have to be Y or N, it can be admin role id (1) or NULL or whatever that will let me know if user is an admin

Answer 1

Well, you can join like this, which will give you the id_role in the result.

SELECT u.*, r.id_role
FROM users u
LEFT JOIN user_roles r
ON u.id=r.id_user

You can add WHERE r.id_role=1 to get just the admins, etc.

But to get the admin as "Y" or "N" as you wanted, you can use an IF on whether the id_role is 1 or not.

SELECT u.*, IF(r.id_role = 1, "Y", "N") as admin
FROM users u
LEFT JOIN user_roles r
ON u.id=r.id_user

Answer 2

I would try this:

select u.id, u.username, if (id_role is null,  'N', 'Y') as is_admin
  from users u
  left outer join user_roles r
    on u.id = r.id_user and r.id_role = 1

But I'm not 100% sure.

Answer 3

select u.id_user, if(count(1) > 0, 'Y', 'N')
from user u left outer join user_roles ur on u.user_id = ur.user_roles
where ur.id_role=1
group by u.id_user

I'm not using mysql, so just googled it has the 'if' function, if that's wrong, replace with DECODE, COALESCE or alike.