CODEWITHSUNDEEP
mysqldatabaseinsertforeign-keys
Yilun Zhang
Yilun Zhang

Reputation: 9018

MySQL: how to insert into a table with two foreign keys linking to two other tables?

I have the following db structure with 3 tables as an example:

Employee

Scenario

Case

Say I already have data in employee and scenario table and I want to insert a new case into the case table so that it fills the foreign keys during the insert. The new case has employee_no in employee table and key in scenario table. I will need to join the two tables using the above values to get employee id and scenario id.

This post (Mysql: How to insert values in a table which has a foreign key) showed how this can be done with one foreign key, how do I do the same thing with two foreign keys?

I currently have something like this that does not work:

INSERT INTO `case` (scenario_id, employee_id, employee_no)
SELECT 
(SELECT scenario.id FROM scenario WHERE scenario.`key` = 'UC01') as scenario_id,
(SELECT employee.id, employee.employee_no FROM employee WHERE employee.employee_no = "0001") as employee_id, employee_no

Upvotes: 1

Views: 2413

Answers (1)

Barmar
Barmar

Reputation: 781059

Join the two tables:

INSERT INTO case (scenario_id, employee_id)
SELECT s.id, e.id
FROM scenario AS s
CROSS JOIN emplopyee AS e
WHERE s.`key` = 'UC01'
AND e.employee_no = '0001'

Upvotes: 2

Related Questions