I lately requested this question.
I've got a relational database with three tables. The very first containts id's that report towards the second. The 2nd consists of id's that report towards the third. The 3rd consists of the outcomes I'm after.
Is it feasible having a single query to query an id within the first table which gives all is a result of the 3rd table that report into it?
My selected solution was:
choose * from table1 t1 join table2 t2 on t1.t2ref = t2.id join table3 t3 on t2.t3ref = t3.id
Give a where clause to find certain rows in table1
where t1.area = 'value'
My new real question is:
I've realized that I have to place in to the three tables too. Things I am coping with is really a reservation system. Can you really write a question that card inserts into three tables directly after it queries them (using joins?).
Also another consideration I've is must i use transactions to make sure that two queries are run simultaneously... both discover that the id's are 'unreserved' after which producing a double booking or it is possible to simpler way?
You need to certainly perform the three card inserts inside a transaction. I'd most likely write a saved procedure to handle card inserts.
Here's one particular saved procedure having a transaction. Note using LAST_Place_ID() to obtain the ID from the formerly placed record. This really is only two tables, however, you should have the ability to extend it to 3 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 also you refer to it as like so:
CALL new_engineer_with_task('Jerry', 'Fernholz', 'email@example.com', 1);
You cannot place into multiple tables with one query, You will need to break up to multiple queries.
You usually use transactions when carrying out multiple updates. If a person update fails you will need to roll back previous effective updates which means you don't violate any unenforced constraints from the relational model.