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.
