eTiMaGo Posted February 24, 2009 Share Posted February 24, 2009 (edited) Hi folks, I'm trying to put together some code which hope to make a contribution, a kind of sales analysis tool, where you select one product and it shows you which customers bought it, but also some stats about the customer like how many order they have in total, what is the latest date of the order they placed, etc. But to do that I need to use a nested SQL query, and I am stumped. Every time I run it, it just locks up MySQL, gotta restart the service... anyway here's my code so far, maybe someone can point out the glaringly obvious mistake I am making? :angry: select c.customers_firstname, c.customers_lastname, c.customers_email_address, count(o.orders_id) as num_orders, max(date_purchased) as last_order, c.customers_newsletter from customers c, orders o where c.customers_id = o.customers_id AND c.customers_id in ( select o.customers_id from orders o, orders_products op where o.orders_id = op.orders_id and op.products_id =1234 ) group by o.customers_id order by last_order desc BTW the sub-query on its own works just fine, as does the outer query if I just skip the whole subquery (and that's a useful query too, shows you juicy info about your active customers!) Edited February 24, 2009 by eTiMaGo Quote Link to comment Share on other sites More sharing options...
eTiMaGo Posted February 25, 2009 Author Share Posted February 25, 2009 couldn't figure it out so I did a very ugly ugly hackjob: load up the customer ids in one query, then loop through it and run each sub query one at a time... yeay... Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.