SQL query help requested

[TheOracle]

No, actually, as said above - you're using the same table so no need to do that.

Without knowing the rest of your codings,

try this one instead:

SELECT customers_id, date_purchased, order_total 
FROM zen_orders
WHERE customers_id = ".(int)$_SESSION['customer_id']." AND date_purchased <> "2006-07-01 00:00:00" AND date_purchased < "2006-08-01 00:00:00"
ORDER BY date_purchased DESC
"; 

That's all you'd need to do really . . .

As for selecting your order_total field, I don't know why it has been selected if you have no intentions of using it under any conditions (unless it is being used below the block you have posted from your personal project file). If not, you can delete it from the SQL Select statement to free up a little bit more SQL server ressources.

[TheOracle]

Sorry. Just discovered a typo I did.

From above,

replace:

WHERE customers_id = ".(int)$_SESSION['customer_id']." AND date_purchased <> "2006-07-01 00:00:00" AND date_purchased < "2006-08-01 00:00:00" 

with:

WHERE customers_id = ".(int)$_SESSION['customer_id']." AND date_purchased > "2006-07-01 00:00:00" AND date_purchased < "2006-08-01 00:00:00" 

[dbrewster]

hmm...

someone at Lunarpages put this up for me, and it does get results, just not the results I need yet, heh. I think it just needs some tweaking.

SELECT zen1.`orders_id`, zen1.`customers_id`, zen1.`customers_name`, zen1.`date_purchased`, zen1.`order_total`
FROM `zen_orders` AS zen1
INNER JOIN `zen_orders` AS zen2 ON zen2.`customers_id` = zen1.`customers_id` AND zen2.`date_purchased` BETWEEN "2006-07-01 00:00:00" AND  "2006-08-01 00:00:00"
INNER JOIN `zen_orders` AS zen3 ON zen3.`customers_id` = zen1.`customers_id` AND zen3.`date_purchased` < "2006-07-01 00:00:00"
ORDER BY zen1.`date_purchased`;

[dbrewster]

Thank you for your help, tried your suggestion:

SELECT customers_id, customers_name, date_purchased, order_total 
FROM zen_orders
WHERE customers_id = ".(int)$_SESSION['customer_id']." AND date_purchased > "2006-07-01 00:00:00" AND date_purchased < "2006-08-01 00:00:00" 
ORDER BY date_purchased DESC;

but this returns 0 rows.

I need to compare customer_id for orders in July to customer_id in orders < July, to see if there are repeat customers in July.

I need to store a selection of [customer_id] for purchase_date < July 1 in an alias, then left join? or inner join? to [selected order data] for purchase_date July 1 to July 30, and show all rows with customer_id matches.

[TheOracle]

Please post your entire project, as an attachment file, I will take a look at it. Otherwise, we might turn around in circle for a long time.

[DrByte]

The lunarpages suggestion is the closest yet. How's this....?

select o.customers_id, o.customers_name, max(o2.date_purchased), count(o.orders_id) as counts
from customers c
LEFT JOIN orders o ON c.customers_id = o.customers_id AND o.date_purchased BETWEEN "2006-07-01 00:00:00" AND "2006-08-01 00:00:00"
LEFT JOIN orders o2 ON c.customers_id = o2.customers_id AND o2.date_purchased < "2006-07-01 00:00:00"
WHERE o.customers_id is not null and o2.date_purchased is not null
group by c.customers_id
ORDER BY counts desc, o.date_purchased DESC;

[dbrewster]

Dr. Byte,

This is getting close!!!

I like your added feature to show the number of orders, but the number is inflated. For instance, in my test database, I placed 17 orders ever, but the result count shows as 66.

Don't I want an inner join? I want all people who ordered in July, who previously ordered. It's just the overlap piece of two groups.

TheOracle: my entire project is just this: a SQL query I can run once and a while in PHPMyAdmin. It isn't a PHP project.

It's just to answer the question: "Who ordered in this month who ordered previously?"

Sucky that I can't use sub-queries on this version of MySQL, that would make it much easier. LunarPages says that MySQL v. 4.0.25 is more stable in a shared hosting environment than 4.1. They offered to move my hosting to another server, but... that's another potential can of worms.

---Diana

[dbrewster]

Here's what my hubby wrote (J2EE expert, they use MS SQL Server on their project.)

This comes close, but gives me multiple lines of the same July transaction.

SELECT a.orders_id, a.customers_id, a.customers_name, a.date_purchased, a.order_total
FROM zen_orders AS a
INNER JOIN zen_orders AS b ON
    a.customers_id = b.customers_id AND
    b.date_purchased < "2006-07-01 00:00:00"
WHERE a.date_purchased BETWEEN "2006-07-01 00:00:00" AND "2006-08-01 00:00:00"
ORDER BY a.date_purchased;

---D

[dbrewster]

This ended up doing the trick:

SELECT DISTINCT a.orders_id, a.customers_id, a.customers_name, a.date_purchased, a.order_total
FROM zen_orders AS a
INNER JOIN zen_orders AS b ON
    a.customers_id = b.customers_id AND
    b.date_purchased < "2006-07-01 00:00:00"
WHERE a.date_purchased BETWEEN "2006-07-01 00:00:00" AND "2006-08-01 00:00:00"
ORDER BY a.date_purchased;

I like DrByte's idea of adding a count to show number of sales per customer, gonna try and work that out.

Thanks to everyone who assisted on this; other ZC'ers might like this query, too.

---Diana

[dbrewster]

This seems to accomplish the goal:

SELECT DISTINCT a.orders_id, a.customers_id, a.customers_name, 
a.date_purchased, a.order_total, count(a.orders_id) AS "Number of Previous Orders"
FROM zen_orders AS a
INNER JOIN zen_orders AS b ON
    a.customers_id = b.customers_id AND
    b.date_purchased < "2006-07-01 00:00:00"
WHERE a.date_purchased BETWEEN "2006-07-01 00:00:00" AND "2006-08-01 00:00:00"
GROUP BY a.customers_id
ORDER BY a.date_purchased
LIMIT 0,100;