OraFAQ Forum: SQL & PL/SQL » Procedure to traverse the table based on fk constraints

Home » SQL & PL/SQL » SQL & PL/SQL » Procedure to traverse the table based on fk constraints (oracle 11g sql developer)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Procedure to traverse the table based on fk constraints [message #630137]

Sat, 20 December 2014 22:49

zenthil86
Messages: 9
Registered: December 2014
Location: fremont

Junior Member

table containing the relationship and contraint details like below
relations_tbl
CHILD_TBL_NAME PARENT_TBL_NAME CHILD_COL_NAME PARENT_FK_COL_NM
A null
B A bid aid
C B cid bid
X Y xid yid
Y A yid yid

entity 1 entity2
A>B>C A>Y>X

requirement to dynamically generate select statement for each table in the CHILD_TBL_NAME column with proper joining conditions
like below

for table A
select * from A

for table B
select * from B , C where B.bid = A.aid

for table C
select * from C, B, A where C.cid=B.bid and B.bid = A.aid

for table X
select * from X, Y, A where X.xid=Y.yid and Y.yid=A.aid

for table Y
select * from Y, A where Y.yid = A.aid

can anyone help me to implement this logic

Report message to a moderator

Re: Procedure to traverse the table based on fk constraints [message #630139 is a reply to message #630137]

Sat, 20 December 2014 22:55

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

>for table B
>select * from B , C where B.bid = A.aid

SQL above is invalid since A.AID does not exist in this statement.

Report message to a moderator

Re: Procedure to traverse the table based on fk constraints [message #630140 is a reply to message #630139]

Sat, 20 December 2014 23:47

zenthil86
Messages: 9
Registered: December 2014
Location: fremont

Junior Member

that was a typo ... A.aid exist in table A.
Basically i want the logic to generate the select statement on each table with proper joining condition based on the constraints. These constraints, parent table, child table are stored in a table. Please help. Thanks in advance.

Report message to a moderator

Re: Procedure to traverse the table based on fk constraints [message #630141 is a reply to message #630140]

Sun, 21 December 2014 00:14

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

RELATIONS_TBL duplicates data the already exists in the Data Dictionary.

This appears to simply be a homework assignment.

Report message to a moderator

Re: Procedure to traverse the table based on fk constraints [message #630146 is a reply to message #630141]

Sun, 21 December 2014 02:23

zenthil86
Messages: 9
Registered: December 2014
Location: fremont

Junior Member

PFA - containing the list of tables with its relationship. We have read the ID column values from each table and insert it into the cross reference table. While reading the ID column values from each table we should generate proper joining condition traversing through its top most parent table.

Attachment: relations_table_csv.csv
(Size: 2.79KB, Downloaded 1016 times)

Report message to a moderator

Re: Procedure to traverse the table based on fk constraints [message #630148 is a reply to message #630146]

Sun, 21 December 2014 02:59

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Sun, 21 December 2014 08:32

1/ There is no attachment
2/ Create your own topic
3/ Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
4/ Always post your Oracle version, with 4 decimals.
5/ Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Report message to a moderator

Re: Procedure to traverse the table based on fk constraints [message #630152 is a reply to message #630148]

Sun, 21 December 2014 09:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>While reading the ID column values from each table we should generate proper joining condition traversing through its top most parent table.

let's stipulate that you now have code that does traverse through these table.
How exactly will the results be used? Do you simply want to look & observe the content of all these tables?

Do you realize that SELECT * FROM PARENT_TABLE, CHILD_TABLE will throw error when same column name exists in both tables?
How do you plain on avoiding this error?

BTW - It is ill advised & unprofessional to use SELECT * in any production code.

Report message to a moderator

Re: Procedure to traverse the table based on fk constraints [message #630159 is a reply to message #630152]

Sun, 21 December 2014 17:21

zenthil86
Messages: 9
Registered: December 2014
Location: fremont

Junior Member

no actually i want to select only the id column values from each table and load into a cross reference table

Report message to a moderator

Re: Procedure to traverse the table based on fk constraints [message #630160 is a reply to message #630159]

Sun, 21 December 2014 17:34

zenthil86
Messages: 9
Registered: December 2014
Location: fremont

Junior Member

lets take example of employee table containing emp id, emp name, supervisor id, when i give an emp id i want to get all the hierarchical supervisor id's. How can i write such query.

Report message to a moderator

Re: Procedure to traverse the table based on fk constraints [message #630162 is a reply to message #630160]

Sun, 21 December 2014 18:02

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

We are not mind readers, so please do as below.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

>How can i write such query.
write SQL SELECT

>when i give an emp id i want to get all the hierarchical supervisor id'
in most places any single employee only has singe supervisor.

http://www.orafaq.com/node/2038

Report message to a moderator

Re: Procedure to traverse the table based on fk constraints [message #630169 is a reply to message #630160]

Mon, 22 December 2014 00:44

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Sun, 21 December 2014 09:59

Michel Cadot wrote on Sun, 21 December 2014 08:32

1/ There is no attachment
2/ Create your own topic
3/ Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
4/ Always post your Oracle version, with 4 decimals.
5/ Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.