charles07 Posted March 8, 2012 Share Posted March 8, 2012 guys, please help i need balance amount of fees all students has to pay. Following is the DB structure. `feepayid` is primary key for paymentstable and paymentregister & `studentid` is primary in studentstable. I have written a query for this, please evaluate the query. paymentstable feepayidclassidpaydatepaymodefeeamountremarksfeediscount 3282012-03-06 19:32:351500hgfhf0 3182012-03-04 19:32:351800hgfhf0 3082012-02-06 19:32:3511200hgfhf0 2982012-02-06 19:32:3511100hgfhf0 paymentregister feepayidpayidtotalfeestudentid 32315002 31235002 30145004 29355004 studentstable studentidstudentname 2john 4mathew 5peter 6mary SELECT d.`studentid`, SUM(a.`totalfee` - (SELECT SUM(`feeamount`) FROM `paymentstable` WHERE a.`feepayid` = `feepayid`)) AS balancefee FROM `studentstable` d LEFT JOIN `paymentregister` a ON a.`studentid` = d.`studentid` GROUP BY d.`studentid`; Quote Link to comment Share on other sites More sharing options...
blacknight Posted March 8, 2012 Share Posted March 8, 2012 did it work? i would have joined the paymentstable insted of having a imbeded select query tho.... Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.