MySQL how to convert multiple value from a reference table

Question

MYSQL - Given that i have 2 tables

tbl_tag

+------+-------------------+
|  ID  |       VALUE       |
+------+-------------------+
| 8001 | Software Engineer |  
| 8002 | C# Developer      |  
| 8003 | PHP Developer     |  
+------+-------------------+

detailed employee

   
id      name      current_pos   current_id  prev_pos         prev_id
----------------------------------------------------------------------------
1001    John      C# Developer       8002   Software Engineer      8001    
1002    Drew      PHP Developer      8003   Software Engineer      8001    
1003    Marco     C# Developer       8002   PHP Developer          8003    
1004    Ashley    Software Engineer  8001   C# Developer           8002   
1005    Tomas     C# Developer       8002   Software Engineer      8001    
1006    Alison    PHP Developer      8003   C# Developer           8002  

i want to insert the id of the given position to the employee table given that the position id can be found from tbl_tag table. I only want the value's id to be inserted into the data.

This should be the output

employee

    
+------+--------+----------------+-------------+
|  id  |  name  | current_pos_id | prev_pos_id |  
+------+--------+----------------+-------------+
| 1001 | John   |          8002  |       8001  |  
| 1002 | Drew   |          8003  |       8001  |  
| 1003 | Marco  |          8002  |       8003  | 
| 1004 | Ashley |          8001  |       8002  |  
| 1005 | Tomas  |          8002  |       8001  |  
| 1006 | Alison |          8003  |       8002  |  
+------+--------+----------------+-------------+

this is my code so far.... i don't know how to make it multiple

INSERT INTO employee (name, current_pos_id ,prev_pos_id)
SELECT 'Michael', tag_id as curr_pos, tag_id as prev_pos
FROM tbl_tag 
WHERE value = 'PHP Developer' 

i want to insert michael with current position is PHP Developer and prev position is C# Developer. Thank you!

                                        
+------+--------+----------------+-------------+
|  id  |  name  | current_pos_id | prev_pos_id |  
+------+--------+----------------+-------------+
| 1007 | Michael|          8003  |       8002  |  
+------+--------+----------------+-------------+

Sorry for my long post, I'm a beginner in MYSQL, and sorry for my english it's not my native language. Thank you!

Answer 1

You should select form two table .. so you could use eg: a cross join each table related to job you need

INSERT INTO employee (name, current_pos_id ,prev_pos_id)
SELECT 'Michael', a.tag_id as curr_pos, b.tag_id as prev_pos
FROM tbl_tag a 
CROSS JOIN bl_tag a 
WHERE a.value = 'PHP Developer' 
AND b.value = 'C# Developer'