MySQL problem

[sepe69]

Hello,

 

I got this SQL clause working which gets from OSC database all customers who have made orders and when the last order was made and when they last logged in to the shop.

 

Now I should have to complete this clause that I can get the product categories from which customers have made orders. Some customers

have made orders from many product categories but only for example last category is enough.

 

There is, as you know, in OSC database, categories, categories_description, orders_products and products_to_categories tables so I think I should use those tables to get the SQL clause to work, but I don't know how.

 

I have been thinking this for many days but I can't find out how to get this working. Could anybody help please ?

 

SELECT

customers.customers_email_address AS EMAIL,

customers.customers_id AS NUMBER_FIELD,

customers.customers_firstname AS FIRSTNAME,

customers.customers_lastname AS LASTNAME,

LEFT( max( customers_info.customers_info_date_of_last_logon ) , 10 ) AS LAST_ACTIVITY_DATE,

LEFT( max( orders.date_purchased ) , 10 ) AS LAST_PURCHASE_DATE,

IF( customers_newsletter =1, 'both', 'customer' ) AS SEGMENT

FROM

customers, customers_info, orders

WHERE

customers.customers_id = customers_info.customers_info_id

AND

customers.customers_id = orders.customers_id

GROUP BY

customers.customers_id

ORDER BY

customers.customers_id, orders.date_purchased

[NodsDorf]

I had to do something similar to get sales. You could try using the order_status in your query as well. I'll give you my notes I made, maybe it will help you. I don't have time today to try and write a new query and all the testing that goes with it.

 

This query list all orders, customer names, billing state, items and quantity, and final price. There will be multiple entries for any order # in which there are more then 1 different item purchased.

Select o.orders_id, o.customers_name, o.customers_state, o.payment_method,
op.products_model, op.products_name, op.products_price,
op.products_quantity, ot.value, o.orders_status
From orders as o
Inner join orders_products as op on o.orders_id=op.orders_id
Inner join orders_total as ot on o.orders_id=ot.orders_id
Where o.orders_id >= 17334 and ot.title = 'Total:'
Order By op.orders_id

 

//

Conditional rules that can be used

Rule #1 o.orders_status IN ('3','100001','100002','100004','100007')

Rule #2 ot.value > 0

Rule #3 change last line "Order By" to "Group By"

 

Adding the conditional rules will effect the query.

 

Rule # 1 The first rule will ignore open orders only finding orders with a status of Delivered, Denied, Refunded, Cancelled. It will not show orders that are marked funny because of paypal reversals or the open orders check/money order that have not been paid.

 

Rule #2 will ignore orders that have a total cost of 0. Adding this rule will ignore orders that were paid using gift certificates or coupon codes which brought the payment value to 0. This information maybe needed if we are to figure in the gift cert and coupon purchases as a loss.

 

Rule #3 this will force the query to 1 line of data per order. Since the original query creates multiple lines of data when there are 2 or more different products purchased, this can be used to ignore the additional products only showing the first item purchased with the total value. This can be useful if you are figuring in just the total sales.

[sepe69]

Thank you for your answer, I will try to apply that.

 

I also have to get categories names, which are in categories_description table. So first I have to solve products that customers have ordered, and then check from products_to_categories and categories_description that what is category name. I have to try something like that.

[sepe69]

Hello,

 

I'm still working with this.

 

So this SQL clause is working ok, but I also have to get categories names, which are in categories_description table. So first I have to solve products that customers have ordered, and then check from products_to_categories and categories_description that what is category name. I have to try something like that.

 

SELECT

customers.customers_email_address AS EMAIL,

customers.customers_id AS NUMBER_FIELD,

customers.customers_firstname AS FIRSTNAME,

customers.customers_lastname AS LASTNAME,

LEFT( max( customers_info.customers_info_date_of_last_logon ) , 10 ) AS LAST_ACTIVITY_DATE,

LEFT( max( orders.date_purchased ) , 10 ) AS LAST_PURCHASE_DATE,

IF( customers_newsletter =1, 'both', 'customer' ) AS SEGMENT

FROM

customers, customers_info, orders

WHERE

customers.customers_id = customers_info.customers_info_id

AND

customers.customers_id = orders.customers_id

GROUP BY

customers.customers_id

ORDER BY

customers.customers_id, orders.date_purchased

 

 

So could anybody help, how can I get categories names from which customers have made orders? Only one category is enough. I really appreciate for your help.