dcsimg
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Find count from 2 tables in a single Query

  1. #1
    Join Date
    Nov 2000
    Location
    New Delhi, India
    Posts
    81
    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

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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

    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Nov 2000
    Location
    New Delhi, India
    Posts
    81

    It doesn't works

    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

  4. #4
    Join Date
    Feb 2002
    Location
    Dallas , Texas
    Posts
    158

    Re: It doesn't works

    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.

  5. #5
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Perhaps something like that?
    Code:
    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

  6. #6
    Join Date
    Nov 2000
    Location
    New Delhi, India
    Posts
    81

    Thumbs up Thanks!!

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width