MySQL插入具有不同列名的表

Question

I have 3 tables. "machines", "products", "machine_products".

The idea in my mind is to store machine information in "machines" table and set "machine_id" column as PRIMARY & UNIQUE and do same with "products" table the column name here is "product_id".

One machine may have one or more product so they both match in "machine_products" table

Table samples are below.

machines table

+------------+------------------+
| machine_id |   machine_code   |
+------------+------------------+
|      1     | C01.C03.C23 M.1  |
+------------+------------------+
|      2     | C07.08.09.10 M.1 |
+------------+------------------+
|      3     | C11.12 MONT.1    |
+------------+------------------+
|      4     | C13.14.21 MONT.1 |
+------------+------------------+
|      5     | C22 MONT.1       |
+------------+------------------+

products table

+------------+--------------+
| product_id | product_code |
+------------+--------------+
|      1     | C01.00       |
+------------+--------------+
|      2     | C01.11       |
+------------+--------------+
|      3     | C01.21       |
+------------+--------------+
|      4     | C03.00       |
+------------+--------------+
|      5     | C03.01       |
+------------+--------------+

machine_products table

+----+------------+------------+
| id | machine_id | product_id |
+----+------------+------------+
|  1 |      1     |     70     |
+----+------------+------------+
|  2 |      1     |     73     |
+----+------------+------------+
|  3 |      1     |     78     |
+----+------------+------------+
|  4 |      1     |     83     |
+----+------------+------------+
|  5 |      2     |     100    |
+----+------------+------------+
|  6 |      2     |     208    |
+----+------------+------------+
|  7 |      3     |     101    |
+----+------------+------------+
|  8 |      3     |     108    |
+----+------------+------------+
|  9 |      3     |     112    |
+----+------------+------------+
| 10 |      4     |     113    |
+----+------------+------------+

My problem is I want to insert data to "machine_products" table by using "machine_code" column data

How do I add records to the machine_products table when what I have are the product_code and machine_code?

Answer 1

Can you try this

Insert into machine_products ( machine_id, product_id) values( (select machine_id from machines where machine_code = $machine_code), (select product_id from products where product_code=$product_code))

You can the substitute $machine_code and $product_code for your own values