Hi..
Please help me to make a query to get results from 3 tables -
Table_CompanyMaster (CompanyCode is unique)
CompanyCode
| Name
| Address
| CIN
|
1
| IBM
| UK
| 334u
|
2
| Microsoft
| US
| 765t
|
3
| TCS
| UK
| 458k
|
4
| Infosys
| Pune
| 123r
|
5
| Zensar
| Hydrabad
| 234t
|
6
| Capital Novas
| Gujarat
| 567h
|
Table_EquitySharesHolders
Sr.No
| CompanyCode ( Foreign KEy)
| Shares
|
1
| 1
| -20
|
2
| 1
| 60
|
3
| 4
| 100
|
4
| 4
| -70
|
Table_OtherShares
Sr.No
| CompanyCode ( Foreign KEy) | Shares
|
1
| 1
| 10
|
2
| 4
| 10
|
3
| 4
| -80
|
4
| 2
| -90
|
Result that I want is -
Sr.No
| Company
| EquityShareCompany
| EquityShareTotal
| OtherShareCompa
| OtherShareTotal
|
1
| IBM
| IBM
| 60-20 = 40
| IBM
| 10
|
2
|
| Infosys
|
|
|
|
3
| Microsoft
|
|
| Microsoft
| -90
|
4
|
|
|
|
|
|
5
|
|
|
|
|
|
6
|
|
|
|
|
|
7
|
|
|
|
|
|
Explanation-
Take 1 company say IBM. (CompanyCode = 1 ).
It has 2 records in Equity SHare Table (Serial No 1 and 2). - 2nd column
Sum of that Equirt shares is => 60-20 = 40. - 3rd column
It has 1 record in Other Share Details. - 4th column
Sum is 10. - 5th column
-----------
How to get this result ??
PLease help....