将数据从一个表插入另一个表 - MySQL

Question

so I'm trying to make a small online store. I have 2 tables: cart_products and order_products. Inside these tables, two types of items that can be added into the cart: promotion and products. These two types of items are stored on different tables called: promotions and products.

Initially all products/promotions are added to the cart table, once the user checks out they are transferred over to the orders table and deleted from the cart table.

If the selected item is a product then the promotion_id value is set to 0 by default. And vice versa if the item selected is a promotion. This is my basic structure:

cart_products

----------------------------------------------------------------------------------
| cart_products_id |    cart_id    |   product_id | promotion_id      | quantity |
----------------------------------------------------------------------------------
| 6                |       1       |   5          | 0                 | 2  
---------------------------------------------------------------------------------

order_products

---

----------------------------------------------------------------------------------
| order_id |   user_id    | product_id | promotion_id      | price    | quantity |
----------------------------------------------------------------------------------

The problem I'm having is trying to LEFT JOIN the products/promotions to get the price of the selected item. This is my query so far.

INSERT INTO order_details (order_id, user_id, product_id, promotion_id, price, quantity)
VALUES(
  '6', 
  '7',
  (SELECT 
    cart_products.product_id, 
    cart_products.promotion_id,
    IF(cart_products.promotion_id = '0', products.price, promotions.price),
    cart_products.quantity

  FROM cart_products


  LEFT JOIN products
  ON cart_products.product_id = products.product_id

  LEFT JOIN promotions
  cart_products.promotion_id = promotions.promotion_id

  WHERE cart_products.cart_id = '6')
)

However, this gives my an error Not unique table/alias. Does anyone know how I can go about this? Any help is greatly appreciated!

Answer 1

Instead of defining values like you have, you can simply select constants so that you can use the INSERT INTO SELECT syntax:

INSERT INTO order_details (order_id, user_id, product_id, promotion_id, price, quantity)
SELECT (
    '6', 
    '7',
    cart_products.product_id, 
    cart_products.promotion_id,
    IF(cart_products.promotion_id = '0', products.price, promotions.price),
    cart_products.quantity
  FROM cart_products
  LEFT JOIN products
    ON cart_products.product_id = products.product_id
  LEFT JOIN promotions
    ON cart_products.promotion_id = promotions.promotion_id
  WHERE cart_products.cart_id = '6'
)

Also, I believe you forgot an "ON" clause on your second left join