MySQL - Insert into three tables

Question

I recently asked this question.

I have a relational database with three tables. The first contains id identifiers that relate to the second. The second contains identifiers related to the third. The third contains the results I am after.

Is it possible with a single query to request the identifier in the first table, which gives all the results from the third table that apply to it?

My chosen solution was:

select * from table1 t1 join table2 t2 on t1.t2ref = t2.id join table3 t3 on t2.t3ref = t3.id

Add a where clause to search for some rows in table 1

where t1.field = 'value'

My new question is:

I realized that I also need to insert into three tables. I am dealing with a reservation system. Is it possible to write a query that is inserted into three tables immediately after its query (using joins?).

Another consideration I have is to use transactions to ensure that two requests are executed simultaneously ... both find that the identifier is “unconditional” and then leads to a double reservation or is there an easier way

Answer 1

You must definitely make three inserts in the transaction. I would probably write a stored procedure to handle inserts.

EDIT:

. LAST_INSERT_ID(), . , .

DELIMITER //
CREATE PROCEDURE new_engineer_with_task(
  first CHAR(35), last CHAR(35), email CHAR(255), tool_id INT)
BEGIN
START TRANSACTION;
   INSERT INTO engineers (firstname, lastname, email) 
     VALUES(first, last, email);

   INSERT INTO tasks (engineer_id, tool_id) 
     VALUES(LAST_INSERT_ID(), tool_id);
COMMIT;
END//
DELIMITER ;

:

CALL new_engineer_with_task('Jerry', 'Fernholz', '[e-mail]', 1);

Answer 2

, .

Answer 3

. , , .