Selecting multiple unrelated data from two tables and insert into one table mysql

Question

This is my scenario

I have a permissions table with the following fields.

id | module | permission

1  | client | add
2  | client | edit
3  | client | delete
4  | someth | edit
5  | someth | delete

employee table

id | status | somestatus
 1 |    act | 1
 2 |    den | 1
 3 |    act | 0
 4 |    den | 1
 5 |    act | 0
 6 |    act | 1

Now what i would need to do is select the employee who have status="act" and somestatus=1 and give them all permissions where module="client"

so the table employee_permissions should have these rows

id | empid | permid | permvalue
1  | 1     | 1      | 1
2  | 1     | 2      | 1
3  | 1     | 3      | 1
1  | 6     | 1      | 1
2  | 6     | 2      | 1
3  | 6     | 3      | 1

This is the query I tried and I'm stuck here

INSERT INTO at2_permission_employee (employee_id,permission_id) 
    SELECT at2_employee.employee_id as employee_id
         , (SELECT at2_permission.permission_id as permission_id 
            FROM at2_permission 
            where at2_permission.permission_module='client'
           ) 
    from  at2_employee  
    where at2_employee.employee_status='Active' 
      and at2_employee.employees_served_admin = 1;

I get the error sub query returns multiple rows which makes sense to me. But I'm not sure how to modify the query to account for iterating over the rows returned by sub query

Take a look at http://stackoverflow.com/questions/20404682/sql-insert-into-from-multiple-tables — indofraiser, Mar 06 '15 at 16:14
I got to the below but there's an issue near 'from' INSERT INTO employee_permissions (empid,permid) (SELECT at2_permission.permission as permid FROM at2_permission where at2_permission.module='client') FROM at2_employee AS p INNER JOIN at2_permission c ON c.Id = p.Id INNER JOIN employee_permissions n ON n.Id = p.Id WHERE at2_employee.status='Act' and at2_employee.somestatus = 1; — indofraiser, Mar 06 '15 at 16:17
'Status' is a keyword so you might want to change the field name — indofraiser, Mar 06 '15 at 16:19

score 1 · Answer 1 · answered Mar 06 '15 at 16:19

If I'm not wrong, like this:

INSERT INTO at2_permission_employee (employee_id, permission_id, permvalue)
SELECT 
  at2_employee.employee_id, 
  at2_permission.permission_id, 
  1  
FROM at2_permission cross join at2_employee
WHERE
  at2_employee.employee_status='Active' 
  and at2_employee.employees_served_admin = 1
  and at2_permission.permission_module='client';

score 0 · Answer 2 · answered Mar 06 '15 at 16:20

It's a bit unclear where the value for permvalue should come from so I hard coded it and used the permission.id for both id and permid, but this query should give you an idea on how to accomplish what you want:

insert employee_permissions (id, empid, permid, permvalue)
select p.id, e.id, p.id, 1
from employee e, permissions p
where p.module = 'client' and e.status = 'act' and e.somestatus = 1;

Selecting multiple unrelated data from two tables and insert into one table mysql

2 Answers2