2. PHPRunner General questions
  3. Getting totals from related tables in one query
This topic is locked

Getting totals from related tables in one query

 		9/28/2006 14:54:08
PHPRunner General questions
D
 Dale author

My inexperience here has really held me up with what should be fairly simple for experienced mysql users, I think. Famous last words.

Im trying to add all the customers purchases and all the customers payments, minus the two and then update the customer record with the balance.
I have three tables here, customer purchases payments. All are linked using customer_id that is a INT 11, and named identically in each table.
I have tried the following mysql query but, if I have a customer with two purchases and one payment, then the payment doubles. If I have a customer that has one purchase and one payment its fine. If I have a customer that has no payments I get nothing.
How can I make my query give me the totals. I have the snippet I need to update the balance field on the customer already done and tested, I just have to get the appropriate totals working properly.
Any help would be greatly greatly appreciated.
SELECT `customer`.`customer_id` ,

`customer`.`name` ,

SUM( `purchases`.`total` ) SUM_purchases,

SUM( `payments`.`amount` ) SUM_payments,

( SUM( `purchases`.`total`) - SUM( `payments`.`amount`)) SUM_owing

FROM `customer` `customer`

INNER JOIN `purchases` `purchases` ON ( `purchases`.`customer_id` = `customer`.`customer_id` )

INNER JOIN `payments` `payments` ON ( `payments`.`customer_id` = `customer`.`customer_id` )

WHERE (`customer`.`status_id` =2 AND `purchases`.`purchase_date` <= now() AND

`payments`.`payment_date` <= now() )

GROUP BY `customer`.`customer_id`
Also, I am trying to ignore any payments or purchases in the future, and do this with only customers with status_id equals 2 with my where clause. That part seems to work fine.
I sure would like to update the customer balance field in the same query if that was possible.

This will go into the events for ListOnLoad for this particular list view.
Help. If there is an existing post, please point me to it. I have done a search and wasnt able to find anything relevant.
T
 		thesofa 9/28/2006

I am not sure, but I think you need to change the inner join to a left join.
D
 		Dale author 9/28/2006

Hi thesofa,
Thanks for your response. I tried that before and found the speed certainly was curtailed, still bearable but a bit longer. I thought I still encountered issues with the end balance. Ive tried the right join too, attempted Union, Exists and on and on. I have no clue what I am doing in mysql. Snippet example here, snippet sample there, This one has just got me.
I will try the left join again, with more thoroughly watch of the result. Ive tried so so many ways, I have created a circular join in my head. Gee's you would have thought they would have thought of that one, maybe there is. ar ar.
Any help is greatly appreciated, this is really frustrating me.
D
 		Dale author 9/29/2006

Well I tried LEFT JOIN, same result, I tried RIGHT JOIN, same result. Both of these ran noticeably slower. The INNER JOIN was the fast. All returned the same result.
There is something wrong with my syntax for the query.
Ive have googled the web for examples and found a wealth of frustrating matches.
Has anyone found a real good site for mysql query samples.??
The problem is I need gather the info off 2 tables.

The inner join works great for 1 table, but I cant get the syntax right to do the 2 tables. Thats when I get my issues with the totals going wacky.
Can anyone help with a suggestion of what I am doing wrong.
T
 		thesofa 9/30/2006

Hi

My join bits look like this

FROM

((((`detentions`

Inner Join `pupils` ON (`pupils`.`idPupil` = `detentions`.`detainee`))

Inner Join `reasons` ON (`reasons`.`idReason` = `detentions`.`reason`))

Inner Join `staff` ON (`staff`.`userid` = `detentions`.`donor`))

Inner Join `dept` ON (`dept`.`idDept` = `detentions`.`department`))

Inner Join `sessions` ON `sessions`.`sess_id` = `detentions`.`sess`



This is for MYSQL

Your where clauses look like this

WHERE (`customer`.`status_id` =2 AND `purchases`.`purchase_date` <= now() AND

`payments`.`payment_date` <= now() )



wheres mine look more like this (or it would do if i used your tables)

WHERE ((`customer`.`status_id` =2) AND( `purchases`.`purchase_date` <= now()) AND

(`payments`.`payment_date` <= now()) )



Maybe this makes a difference?
Alexey admin 10/2/2006

Dale,
I'm not sure if this query is possible using JOINS.

Try using subqueries instead. Here is the SQL code:

SELECT `customer`.`customer_id` ,

`customer`.`name` ,

(select SUM(total) from purchases where customer_id = `customer`.`customer_id` and purchase_date <= now() ) as SUM_purchases,

(select SUM(amount) from payments where customer_id = `customer`.`customer_id` and payment_date <= now()) as SUM_payments,

(select SUM(total) from purchases where customer_id = `customer`.`customer_id` and purchase_date <= now()) -

(select SUM(amount) from payments where customer_id = `customer`.`customer_id` and payment_date <= now()) as SUM_owing

FROM `customer`

WHERE `customer`.`status_id` =2


Please note that subqueries work with MySQL 5 only.
D
 		Dale author 10/2/2006

Thanks for the responses. I will try them and see if I can get over this issue.
Thanks Alexey for the note about subqueries ONLY in Mysql 5. My host is not running that version, I may have burned a lot of time trying subqueries with the older version. I appreciate that.
Thanks thesofa for your suggestions. I will clean up my where's and give it a test. I'll drop my results into the forum on this one. I know the time it takes to respond to these posts, so I truely appreciate the suggestions.
Thanks again.
T
 		thesofa 10/2/2006

Just as a by the way, I find using something like Navicat or PHPMyadmin allows easy query building and testing, so much easier than trying to work each one out and then bug hunting with a complex set of sub queries.

HTH