Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Posts
    58

    Unanswered: how to make this query?

    Hello,

    I have a table structure like this where TB1 ... TD9 represent pks and fks

    Now, I would like to know for TABLEA - TA1, how many TDs exist. Similarily for any other primary key in TableA. Can we write a query to do that? Shall appreciate your ideas.

    Thanks in advance.



    TABLEA TA1 TB1
    TB2
    TABLEB TB1 TC1
    TC2
    TB2 TC3
    TC4

    TABLED TC1 TD2
    TD3
    TC2 TD4
    TD5
    TC3 TD6
    TD7
    TC4 TD8

  2. #2
    Join Date
    May 2003
    Posts
    58
    Hello,

    I have a table structure like this where TB1 ... TD9 represent pks and fks

    Now, I would like to know for TABLEA - TA1, how many TDs exist. Similarily for any other primary key in TableA. Can we write a query to do that? Shall appreciate your ideas.

    Thanks in advance.

    Code:
    
    TABLEA  TA1 TB1
                        TB2
    TABLEB  TB1 TC1
                        TC2
                 TB2  TC3
                        TC4
    
    TABLED  TC1  TD2
                        TD3
                 TC2 TD4
                       TD5
                 TC3 TD6
                        TD7
                 TC4 TD8

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: how to make this query?

    Sounds like you just want to do this:

    select ta1, count(*)
    from tablea
    group by ta1;

    I don't understand this part of your question though: "Similarily for any other primary key in TableA", because a table only has one primary key, and a primary key value will always appear in exactly one row.

  4. #4
    Join Date
    Dec 2003
    Posts
    2
    In table a TA1 has TB1 and TB2 as foreign keys.
    TB1 has TC1 and TC2. TC1 has TD2 TD3. This grows like a tree .....

    Now, for TA1, the query should output TD2 .. TD8

    That means for a given key of first table, we have to get all the keys of last table (foreign keys that exist in last but one table)

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778

    Recursive?

    A simple query is below. (This was just to get the results)

    One expected / correct method to achieve this is to use a PL/SQL procedure that implements recursion. I would also recommend having a single table containing the PK-FK relationships.


    tableA

    C1 C2
    ---------- ----------
    TA1 TB1
    TA1 TB2

    TableB and TableC follow the same structure

    SQL> select tableC.c2
    2 from tableA, tableB, tableC
    3 where tableA.c2 = tableB.c1 AND
    4 tableC.c1 = tableB.c2 AND
    5 tableA.c1 = 'TA1';

    C2
    ----------
    TD2
    ..
    TD8
    Last edited by r123456; 12-05-03 at 10:19.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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