Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

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

mysql join query

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

New User

5+ Year Member

joined:Feb 9, 2010
posts: 26
votes: 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.
7:59 am on Oct 19, 2010 (gmt 0)

New User

5+ Year Member

joined:Feb 9, 2010
posts: 26
votes: 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)
8:15 am on Oct 19, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Apr 30, 2007
votes: 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.
8:46 am on Oct 19, 2010 (gmt 0)

New User

5+ Year Member

joined:Feb 9, 2010
posts: 26
votes: 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;

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members