[chatguy2020]

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

[chatguy2020]

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 

[andrewst]

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.

[solprovider]

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)

[r123456]

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