homepage Welcome to WebmasterWorld Guest from 54.234.0.85
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
select items in all orders tbl that are also in manf tbl
mysql join query
sukebe



 
Msg#: 4218700 posted 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



 
Msg#: 4218700 posted 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

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4218700 posted 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



 
Msg#: 4218700 posted 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;

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved