mookiha
-
Posts
4 -
Joined
-
Last visited
Never
Posts posted by mookiha
-
-
[quote author=Barand link=topic=108377.msg436450#msg436450 date=1158581908]
If you want all accounts, even if they have no points, you need
[/quote]
Thanks, I didnt fully test both queries yet, now I understand a little more the effects of the two.
Then I could slip in a
[code]HAVING records > 0[/code]
to go back to accounts with points only -
Thanks, I didn't think about the ordering.
Have any recommendations on MySQL books geared towards noobs? -
I have 3 tables
mypoints <- could be thousands of records
id
user_id
cat_id
points
status
mycategories <- 10-20 records
id
description
acct_id
status
myaccounts <- a few records
id
description
status
Simple structure of myaccounts which contains mycategories which contains mypoints.
For one particular user I would like to retrieve the summary information of points, I have a working sql query but would like to know if it is the best way of doing it. I have little understanding of joins and such.
Here is what I have so far:
[code]SELECT a.id, a.description, SUM( p.points ) AS currentbalance, SUM( IF( p.status = 0, 0, p.points ) ) AS availablebalance, COUNT( p.points ) AS records
FROM myaccounts AS a
LEFT JOIN mycategories AS c ON c.acct_id = a.id
LEFT JOIN mypoints AS p ON p.cat_id = c.id
WHERE a.status = 1
AND c.status = 1
AND p.user_id = $user_id
GROUP BY a.id
ORDER BY a.ordering[/code]
This returns all account summary info if a user has points
So then I got to experimenting, the following works also but because of my lack of understanding the internal workings of mysql I was wondering which is better for the sql engine.
[code]SELECT a.id, a.description, SUM( p.points ) AS currentbalance, SUM( IF( p.status = 0, 0, p.points ) ) AS availablebalance, COUNT( p.points ) AS records
FROM mypoints AS p
LEFT JOIN mycategories AS c ON c.id = p.cat_id
LEFT JOIN myaccounts AS a ON a.id = c.acct_id
WHERE a.status = 1
AND c.status = 1
AND p.user_id = $user_id
GROUP BY a.id
ORDER BY a.ordering[/code]
Is it my understanding that the first query is better? Or maybe neither is and someone has a better way of doing this.
Best way to retrieve aggregate summary info
in MySQL Help
Posted
Thanks, you folks have been very helpful.