How to insert related data parts in two tables?

Question

I have an employee table with

empID(name, address and dateOfBirth)
Position(positionID, positionName)
empPosition(empID, positionID, datePositionChanged, INDEX(empID), INDEX(positionID))

I have inserted in employee table as:

INSERT INTO `mario`.`employee` 
  (`empID`, `fName`, `lName`, `address`, `postCode`, `dateOfBirth`) 
VALUES 
  (NULL, 'Paul', 'Roberts', '221 heaventown street', '1994-12-01');

and in position table as:

INSERT INTO `mario`.`position` 
  (`positionID`, `positionName`, `datePositionChanged`) 
VALUES 
  (NULL, 'Manager', '2013-12-01');

Obviously, there is no relationship between Paul and manager. Any suggestions on how should I insert it ?

(I am very new student; just learning in mySQL).

@MichaelBerkowski Not when it's a *many-to-many* relationship (hence the `empPosition` [*junction table*](http://en.wikipedia.org/wiki/Junction_table)) — Phil, Dec 03 '13 at 02:27
@Phil I skipped over the `empPosition` when scanning the text. (didn't read the question thoroughly) — Michael Berkowski, Dec 03 '13 at 02:28
possible duplicate of [LAST\_INSERT\_ID() MySQL](http://stackoverflow.com/questions/3837990/last-insert-id-mysql). See the first part of the accepted answer (setting a variable) — Phil, Dec 03 '13 at 02:29
@MichaelBerkowski I want the position and employee to have many to many relationship based on time. — Shasi, Dec 03 '13 at 02:46
@Phil , It doesnt look similar, as i understand. Any help be appreciated. — Shasi, Dec 03 '13 at 02:47
Assuming the IDs in the `employee` and `position` tables are auto-increment (hence the `NULL` insert values), you would insert into the first (`employee`) and save the `LAST_INSERT_ID()` into a variable. Rinse / repeat for `position` (saving into a different variable) then insert both saved IDs into `empPosition`. — Phil, Dec 03 '13 at 02:51

score 1 · Answer 1 · answered Dec 03 '13 at 11:32

If your position table will be like the master table for all possible positions assign a position Id for each row and empPosition table you can have a mapping between employee and position.

INSERT INTO mario.position (positionID, positionName, datePositionChanged) VALUES (1, 'Manager', '2013-12-01');

INSERT INTO mario.employee (empID, fName, lName, address, postCode, dateOfBirth) VALUES (1, 'Paul', 'Roberts', '221 heaventown street', '1994-12-01');

INSERT INTO mario.empPosition' ('empID', 'positionID') values (1,1);

score -1 · Answer 2 · edited May 31 '14 at 19:09

-1

It seems Table Position should has a primary key of something like PositionId. This is then inserted into empPosition. In other words empPosition is sort of a look-up table which references the primary keys EmployeeId and PositionId. This table can then be queried to get a position by employeeId, etc.

edited May 31 '14 at 19:09

marc_s

732,580
175
1,330
1,459

answered Dec 03 '13 at 02:27

David J Barnes

506
9
15

1

positionID and empID are primary keys in position and employee tables. – Shasi Dec 03 '13 at 02:43

How to insert related data parts in two tables?

2 Answers2