Find count from 2 tables in a single Query

[ShrutiM]

Hi!!

I want to find the count of records from two tables corresponding to each record in the third table
Here is what i mean:

Table1
pk_id1 varchar2(10)

Table2
pk_id2 varchar2(10)
pk_id1 varchar2(10)

Table3
pk_id3 varchar2(10)
pk_id1 varchar2(10)

Table2 and Table3 has foreign key refering
the pk_id1 of table 1
Table2 and Table3 can have many records corresponding
to single pk_id1 of Table 1

I tried this query:

Select
count(a.pk_id3) cnt_table3,
count(b.pk_id2) cnt_table2,
a.pk_id1
from table3 a, table2 b, table1 c
Where c.pk_id1 = b.pk_id1
and c.pk_id1 = c.pk_id1
Group by c.pk_id1

But his doesn't seems to work. Please help!!

Thanks

Regards
Shruti

[jmodic]

Check the following corrections to your query (in bold):

Select
count(a.pk_id3) cnt_table3,
count(b.pk_id2) cnt_table2,
c.pk_id1
from table3 a, table2 b, table1 c
Where c.pk_id1 = b.pk_id1
and c.pk_id1 = a.pk_id1
Group by c.pk_id1

[ShrutiM]

Thanks JModic!!

But I'd tried this. I think there was some typing mistakes but the results are not comming correct.
See for your self.

SELECT PK_ID1 FROM Table1 ORDER BY PK_ID1;

PK_ID1
--------------
1000000001
1000000002
1000000003
1000000004
1000000005
1000000006
1000000007
1000000008
1000000009

SELECT PK_ID2, PK_ID1 FROM Table2 ORDER BY PK_ID1, PK_ID2;

PK_ID2 PK_ID1
-------------- ----------
1000000004 1000000001
1000000010 1000000001
1000000002 1000000002
1000000003 1000000002
1000000008 1000000002
1000000009 1000000002
1000000006 1000000003
1000000007 1000000004
1000000011 1000000005
1000000012 1000000005
1000000013 1000000006
1000000014 1000000006
1000000015 1000000007
1000000016 1000000007
1000000017 1000000008
1000000018 1000000008
1000000019 1000000009

SELECT PK_ID3, PK_ID1 FROM Table3 ORDER BY PK_ID1, PK_ID3;

PK_ID3 PK_ID1
-------------- ----------
1000000004 1000000001
1000000003 1000000002
1000000005 1000000005
1000000009 1000000005
1000000006 1000000006
1000000007 1000000007
1000000008 1000000008

The Query we discussed Gives something wrong:

Select
count(a.pk_id3) cnt_table3,
count(b.pk_id2) cnt_table2,
c.pk_id1
from table3 a, table2 b, table1 c
Where c.pk_id1 = b.pk_id1
and c.pk_id1 = a.pk_id1
Group by c.pk_id1

CNT_TABLE3 CNT_TABLE2 PK_ID1
--------------- --------------- --------------
2 2 1000000001
4 4 1000000002
4 4 1000000005
2 2 1000000006
2 2 1000000007
2 2 1000000008

While if we run individual counts on the two table
we get the following:

Select
count(PK_ID2) cnt_table2, PK_ID1
from table2
Group by PK_ID1

CNT_TABLE2 BRD_ID
--------------- --------------
2 1000000001
4 1000000002
1 1000000003
1 1000000004
2 1000000005
2 1000000006
2 1000000007
2 1000000008
1 1000000009

Select
count(PK_ID3) cnt_table3, PK_ID1
from table3
Group by PK_ID1

CNT_TABLE3 BRD_ID
--------------- --------------
1 1000000001
1 1000000002
2 1000000005
1 1000000006
1 1000000007
1 1000000008

Thanks for the support.. But I think there is something more to be done.

Regards
Shruti

[nishant]

hi,
============
select master.pk_id1 ,count(table2.pk_id1),count(table3.pk_id1)
from table2,table3,table1 master
where master.pk_id1=table1.pk_id1
and master.pk_id1=table2.pk_id1
group by master.pk_id1;
============
peace
nishant.

[ales]

Perhaps something like that?

Select
count(distinct a.pk_id3) cnt_table3,
count(distinct b.pk_id2) cnt_table2,
c.pk_id1
from table3 a, table2 b, table1 c
Where c.pk_id1 = b.pk_id1(+)
and c.pk_id1 = a.pk_id1(+)
Group by c.pk_id1

[ShrutiM]

Thanks Ales!!!

I also found the answer this very query was solution to the problem.

Hope this doesn't have problems regrding the performance.

Thanks

Regards
Shruti