Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kalsee
Starting Member
5 Posts |
 Posted - 2010-12-19 : 07:26:33
|
| I have 3 tables: people, loans, and deposits. people: id, name, address etc. loans and deposits tables: id, sum,date etc.each person may have any number of loans or deposits, I want to know how much sum total money any specific person has in his account now.for the loans:select people.id,sum(loans.sum) from people INNER JOIN loans ON people.id=loans.id group by people.idi did the same for the deposits table:select people.id,sum(deposits.sum) from people INNER JOIN deposits ON people.id=deposits.id group by people.idHow can I combine these two to get ONE number for each id? I know there is something called a nested select, but I can't figure out how to add it in this case |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2010-12-19 : 08:37:40
|
You could use nested selects, but in this case it may be easier to join all three tables, for example like this:select p.id, isnull(sum(d.sum),0) - isnull(sum(l.sum),0) as NetAmountfrom people as p left join deposits as d on d.id = p.id left join loans as l on l.id = p.idgroup by p.id I am using left joins because not all people may have loans and not all may have deposits. I used aliases (eg. "people as p") which allows me to refer to the people table with the alias p. |
 |
|
|
kalsee
Starting Member
5 Posts |
Posted - 2010-12-20 : 01:51:42
|
Thanks, this looks like exactly what I need. I can't add it in right now but I'll let you know if it doesn't work  .out of curiosity, what is the isnull(sum, 0) function? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-12-20 : 05:29:17
|
| If sum is null, make it 0, else leave as it isMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
nested select possible here? how?