mysql - 'Subquery returns more than 1 row' when INSERT multiple values from two different tables into a 'bridge table'

Question

I have 3 tables:

1. users
2. roles
3. users_roles (the 'bridge table')

I'm trying to execute this query:

INSERT INTO `db`.`users_roles` (`user_id`, `role_id`)
VALUES ((SELECT id FROM `db`.`users` WHERE username like '%@gmail.com'), (Select id from roles where authority="can_view"));

Since there are hundreds of user_id with the username like %gmail.com, I'm getting the error "Subquery returns more than 1 row". Also, there's only one authority, that's why i used the = operator. I'm aware I should use the IN operator when working with multiple values, but I don't seem to know how to properly write this query with the first SELECT.

How can i solve this problem? Help!

Answer 1

Just use insert . . . select:

INSERT INTO db.users_roles (user_id, role_id) 
    SELECT u.id, r.id
    FROM db.users u JOIN
         roles r
         ON u.username like '%@gmail.com' AND
            r.authority = 'can_view';