Oracle Query - Results from 3 tables

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....

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Vishal Jadhav11y ago
Accepted answer
Hi Riddhi,

Please check below.

select a.name, equity.name, equity.othrshares, others.name, others.othrshares
from CompanyMaster a
left join (select b.CompanyCode, c.name, b.equityshares from
(select CompanyCode, sum(Shares) as equityshares from EquitySharesHolders group by CompanyCode) b
inner join CompanyMaster c on b.CompanyCode=c.CompanyCode)
equity on equity.CompanyCode = a.CompanyCode
left join (select b.CompanyCode, c.name, b.othrshares from
(select CompanyCode, sum(Shares) as othrshares from OtherShares group by CompanyCode) b
inner join CompanyMaster c on b.CompanyCode=c.CompanyCode)
others on others.CompanyCode = a.CompanyCode;

Oracle Query - Results from 3 tables - URGENT. PLZZ HELP