Insert using LEFT JOIN and INNER JOIN

Question

Hey all i am trying to figure out how to go about inserting a new record using the following query: ``` SELECT user.id, user.name, user.username, user.email, IF(user.opted_in = 0, 'NO', 'YES') AS optedIn FROM user LEFT JOIN user_permission AS userPerm ON user.id = userPerm.user_id ORDER BY user.id; ``` My `INSERT` query so far is this: ``` INSERT INTO user SELECT * FROM user LEFT JOIN user_permission AS userPerm ON user.id = userPerm.user_id; ``` However, i am not sure how to do `VALUE('','','','', etc etc)` when using left and inner joins. So what i am looking to do is this: `User` table: ``` id | name | username | password | OptIn -------------------------------------------------------------------- 562 Bob Barker bBarker BBarker@priceisright.com 1 ``` And also the `user_permission` table ``` user_id | Permission_id ------------------------- 562 4 ``` UPDATE So like this? ``` INSERT INTO user (name, username, password, email, opted_in) VALUES ('Bbarker','Bbarker','blahblahblah','Bbar…

Accepted Answer

You have to be specific about the columns you are selecting. If your `user` table had four columns `id, name, username, opted_in` you must select exactly those four columns from the query. The syntax looks like:

```
INSERT INTO user (id, name, username, opted_in)
SELECT id, name, username, opted_in 
FROM user 
LEFT JOIN user_permission AS userPerm ON user.id = userPerm.user_id
```

However, there does not appear to be any reason to join against `user_permission` here, since none of the columns from that table would be inserted into `user`. In fact, this `INSERT` seems bound to fail with primary key uniqueness violations.

MySQL does not support inserts into multiple tables at the same time. You either need to perform two `INSERT` statements in your code, using the last insert id from the first query, or create an `AFTER INSERT` trigger on the primary table.

```
INSERT INTO user (name, username, email, opted_in) VALUES ('a','b','c',0);
/* Gets the id of the new row and inserts into the other table */
INSERT INTO user_permission (user_id, permission_id) VALUES (LAST_INSERT_ID(), 4)
```

Or using a trigger:

```
CREATE TRIGGER creat_perms AFTER INSERT ON `user`
FOR EACH ROW
BEGIN
  INSERT INTO user_permission (user_id, permission_id) VALUES (NEW.id, 4)
END
```

Insert using LEFT JOIN and INNER JOIN

Solution

Problem