How can I make this SUM query by LEFT JOINing 2 tables?

[ConfusedOfLife]

Hi

I'm having a parent and a child table:


mysql> EXPLAIN child;
+-----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| parent_id | int(11) | | MUL | 0 | |
| quantity | int(11) | | | 0 | |
| status | enum('r','p') | | | r | |
+-----------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> EXPLAIN parent;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | | PRI | NULL | auto_increment |
| name | varchar(30) | | | | |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.03 sec)



In the child table I'm inserting all quantities related to the parent table. Status in here means whether the quantity was received ("r") or was paid ("p"). So, for each user in the parent table I wana find out how much he/she received and how much he/she spent to find out the total money he/she has.

Ok, writing 2 queries for this purpose is easy, but what if I wana use only one query? Look at this:


SELECT parent.*, SUM(ch1.quantity) AS RTotal, COUNT(ch2.quantity) FROM parent

LEFT JOIN child ch1 ON parent.id = ch1.parent_id
LEFT JOIN child ch2 ON parent.id = ch2.parent_id

WHERE ch1.status = "r" AND ch2.status = "p"
GROUP BY parent.id, parent.name


Hey, the second COUNT is only because I show you that when I LEFT JOIN a table to itself and then use GROUP BY, it doesn't understand the group by. I mean let's say I have these values in parent and child tables:


parent:

id | name
-----------------
1 me
2 you


child:
parent_id |quantity |status
1 30 r
1 40 p
2 60 r
2 50 p
1 90 r
2 90 r
1 200 r
1 20 p


running the query that I wrote results in:

id | name | RTotal | COUNT( ch2.quantity )
1 me 640 6
2 you 150 2


As you see COUNT(ch2.quantity) is the multiplication of number of "r" for 1 by number of "p" for 1, and it's the same for id #2. I don't know why, it seems that the group by doesn't work when you LEFT JOIN a table to each other.

[raf]

i don't understand why you need a left join ...

isn't it just

select parent.name, sum(child.quantity), status from parent inner join child on parent.id=child.parent_id group by parent.name, status order by parent.name asc, status asc

inside your server side scripting, you can then easely process the recordset to get the real total. most easers will even be intrested in the detail + total

but actually, your quantity should be a signed column so that

select parent.name, sum(child.quantity) from parent inner join child on parent.id=child.parent_id group by parent.name by parent.name asc

returns what you need.

[ConfusedOfLife]

Yeah! You're right again! But it's much easier if I could do such sort of thing, you know, I could for example select all those who are eligible to get money, I mean those whose receive are more than their payment!

[raf]

i don't quite understand what you mean...
do you mean: run a query to select all users with a positive total?

why don't you use -1 and 1 as status? then you could probably do

select parent.name, sum(child.quantity*child.status) as totalbalance from parent inner join child on parent.id=child.parent_id group by parent.name having totalbalance > 0 order by parent.name asc

and it would offer you the same possebilitys for other querys then the 'p' and 'r'

[ConfusedOfLife]

Yes, yes, you're right again! I really have nothing else to say :thumbsup: