Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

SQL Debugging help!


eTiMaGo

Recommended Posts

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 by eTiMaGo
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...