select items in all orders tbl that are also in manf tbl

Forum Moderators: open

Message Too Old, No Replies

select items in all orders tbl that are also in manf tbl

mysql join query

sukebe

7:04 am on Oct 19, 2010 (gmt 0)

Hello, I have this query:
SELECT order_details.product_code AS 'SKU', SUM(order_details.amount), SUM(order_details.price) AS 'Selling Price'
FROM order_details, orders
WHERE (order_details.product_code LIKE 'SM%' OR order_details.product_code LIKE 'AZ1%')
AND orders.order_id = order_details.order_id
GROUP BY substring(order_details.product_code, 1, 9)
ORDER BY order_details.product_code, order_details.amount DESC;

which works. However, the LIKE parts are haphazard. So, I've added manufacture.id to the products table (not in this query) and have to change the LIKE parameters with something like:
WHERE (order_details.product_code = products.product_code AND products.mfr = 'mazda')
AND ...

I've got brain freeze on this. Of course I have to add products.mfr to the select line. I suppose I have to join the mfr id to the order_details... but I'm stuck.

Please help.

sukebe

7:59 am on Oct 19, 2010 (gmt 0)

this doesn't work:
SELECT cscart_order_details.product_code AS 'SKU', SUM(cscart_order_details.amount) AS 'amount', SUM(cscart_order_details.price) AS 'Selling Price'
FROM cscart_order_details, cscart_orders, cscart_products
WHERE cscart_products.manufacturer_id = 5
AND (cscart_orders.status = 'P' OR cscart_orders.status = 'C')
AND cscart_orders.order_id = cscart_order_details.order_id
GROUP BY substring(cscart_order_details.product_code, 1, 9)

enigma1

8:15 am on Oct 19, 2010 (gmt 0)

Looks like you don't have a reference in your order tables for the manufacturer. Then again order related tables describe history they should never relate to active entities of your store (products, manufacturers etc). You need to have another column in your order_details table perhaps and when an order is placed, to save the manufacturer name.

sukebe

8:46 am on Oct 19, 2010 (gmt 0)

OK, I think I have it in this:
SELECT cscart_order_details.product_code AS 'SKU', cscart_order_details.order_id AS 'ID', cscart_order_details.amount AS 'AMOUNT', cscart_order_details.price AS 'Selling Price'
FROM cscart_order_details
LEFT JOIN cscart_orders
ON cscart_orders.order_id = cscart_order_details.order_id
LEFT JOIN cscart_products
ON cscart_products.product_code = cscart_order_details.product_code
WHERE (cscart_orders.status = 'P' OR cscart_orders.status = 'C')
AND cscart_orders.timestamp BETWEEN UNIX_TIMESTAMP('2010-09-01 01:37:02') AND UNIX_TIMESTAMP('2010-09-02 11:01:01')
AND cscart_products.manufacturer_id = 1
ORDER BY cscart_order_details.product_code, cscart_order_details.amount;