Insert into a table from two different tables

Question

Here I have 3 tables.

• sbs_users
• sbs_permissions
• sbs_user_permissions

let's say the data of each table are:

- sbs_users(table1)
userid    username
1         john
2         albert

- sbs_permissions(table2)
permissionid    permission
1               create
2               edit

- sbs_user_permissions(table3)
upid(autoid)    userid    permissionid

What I wanna do is insert into table3 with the all data from table2 but only get the last id in table1.

So expected after insert is:

- sbs_user_permissions(table3)
upid(autoid)    userid    permissionid
1               2         1
2               2         2

Thanks in advance

Answer 1

You probably need something like this...

INSERT INTO sbs_user_permissions (userid, permissionid)

SELECT u.userid, p.permissionid
FROM sbs_users u
INNER JOIN sbs_permissions p ON 1=1
WHERE u.userid = 2;

Answer 2

If I read you correctly, you want to give all permissions to the user with the max user id? If so, this:

insert into user_permissions select u.id, p.id from
  users u cross join permissions p
  where u.id = (select max(id) from users);

demo here