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!
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