database - mySQL - Insert into three tables

Question

I recently asked this question.

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

Is it possible with a single query to query an id in the first table which gives all results from the third table that relate 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 certain rows in table1

where t1.field = 'value'

My new question is:

I have realised that I need to insert into the three tables too. What I am dealing with is a reservation system. Is it possible to write a query that inserts into three tables directly after it queries them (using joins?).

Also another consideration I have is should I use transactions to ensure that two queries are run at the same time... both find that the id's are 'unreserved' and then resulting in a double booking or is there a more simple way?

Answer 1

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

EDIT:

Here is an example of a stored procedure with a transaction. Note the use of LAST_INSERT_ID() to get the ID of the previously inserted record. This is only two tables, but you should be able to extend it to three tables.

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 ;

And you call it like so:

CALL new_engineer_with_task('Jerry', 'Fernholz', '[email protected]', 1);