FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


mysql query based on 2 fields using 3 different tables





davidv
Hi,

I've been sweating it out for the past few days on this one question.

Quote:
Complex grouping

Write a MySQL query to find the total available balance by product and branch where there is more than one account per product and branch pair. Your query should return three columns: the product name, the branch name, and the total available amount. The results should be ordered by total balance (lowest to highest).


We're using the database LearningSQLExample. Here's a link to where you can download it.

http://examples.oreilly.com/learningsql/

What I did was this
Code:
SELECT p.name, b.name, sum(a.avail_balance)
FROM account a LEFT OUTER JOIN branch b
ON a.open_branch_id = b.branch_id LEFT OUTER JOIN product p
ON p.product_cd = a.product_cd LEFT OUTER JOIN account aa
ON aa.account_id = a.account_id
GROUP BY p.name, b.name;

but it doesn't work. There's also a few things I didn't do that the question asked for. Can anybody help? It'd be helpful if you could also include what you did.

Thanks Very Happy
jmraker
I would try taking out the grouping and the sum
Code:
SELECT p.name, b.name, a.avail_balance
FROM account a LEFT OUTER JOIN branch b
ON a.open_branch_id = b.branch_id LEFT OUTER JOIN product p
ON p.product_cd = a.product_cd LEFT OUTER JOIN account aa
ON aa.account_id = a.account_id

to better see which records it's using, also you didn't order the records by total balance from lowest to highest

If it query didn't work at all there would be an error message that usually provides a clue to where the clue is in the statement. http://us2.php.net/mysql_error
davidv
jmraker wrote:
I would try taking out the grouping and the sum
Code:
SELECT p.name, b.name, a.avail_balance
FROM account a LEFT OUTER JOIN branch b
ON a.open_branch_id = b.branch_id LEFT OUTER JOIN product p
ON p.product_cd = a.product_cd LEFT OUTER JOIN account aa
ON aa.account_id = a.account_id

to better see which records it's using, also you didn't order the records by total balance from lowest to highest

If it query didn't work at all there would be an error message that usually provides a clue to where the clue is in the statement. http://us2.php.net/mysql_error


But I want to bucket the accounts based on the branch and product pair and them sum the available balance of each bucket. The sorting part can be left out once the other tasks have been completed. I'm assuming that a pair is the branch name and product name that an account has, or maybe I'm going at it the wrong way? All pairs are the possible combinations made between product name and branch?
Lucifer_iix
Can you post your DB CREATE SQL STATEMENT?

And make unique field names like.....

tb_tblTABLES:
tblPK
tblTABLE
tblTBNfk

tb_tbnTABLENAMES:
tbnPK
tbnNAME

vw_TABLES: (SELECT * FROM tb_tblTABLES JOIN tb_tbnTABLENAMES ON tblTBNfk = tbnPK)

It makes cleaner code and you can use the * while developing... and designing... afterwards replace the *

2.. to go....
Lucifer_iix
And what does the SQL error say?

And what happens when you remove the groop by?
davidv
I've solved the problem. I had to slightly modify my query to something else. There wasn't a need to inner join itself. Thanks for the responses guys.
Related topics
How To : Improve Your PHP Programming
Post- Nuke
Help with mysql query
AJAX tutorial [2nd part now updated]
Creating a Search Script with PHP for MySQL
PhpNuke Installation Tutorial
Need Help Yet again :: Comment system, data from 3 tables !!
Activation and approval !!
Authentication Module
Mysql wont connect Hmmm
mysql insert into multiple tables at once?
MySQL Query help
MySQL Query Cache
Creating tables with Mysql queries
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.