how can i get the link attributes between two tables

I don't understand - you KNOW the relationship between PARTS and SHIPS, you told us what it is already!

You do not need to inspect the data dictionary (ALL_CONSTRAINTS etc.) to find out which parts belong to which ship, you just need to write a SELECT that joins the tables together - e.g.

select s.hull_id, p.part_id
from   ships s
       equipment e,
       equipment_parts ep,
       parts p
where  s.hull_id = e.hull_id
and    e.e_id = ep.e_id
and    ep.part_id = p.part_id;

thanks for the reply.

am building a charting application using Java. i accept the attribute for X-axis and an attribute for Y-axis.

so, to begin with:
i wud only know the two attribute names and the their respective table names.

so, my task is to form a query - based on this information.

am able to build a query easily if the two tables have direct parent-child relationship. but as in this case - if there is one/more intermediate tables in the middle, how do i get the 'linking' attributes?

hope i have explained my problem clearly enuf now.

thanks

--$uDhA

Sorry, I misunderstood you initially.  I don't think this will be an easy task - it's not unlike a route finder program that tells you how to get from one location to another by road.  I've always wondered how they work!  I can't see any shortcuts here (there probably are some), so I think you would have to have an iterative process like this pseudo-PL/SQL:

PROCEDURE find_path( a varchar2, b varchar2, path ???)
IS
BEGIN
  -- Get all the tables to which table A has foreign keys,
  -- and all the tables that have foreign keys to table A
  FOR r IN (SELECT table_name ...)
  LOOP
    IF r.table_name = b THEN
      -- Path to B found
      store_path( path );
    ELSE
      -- Search for a path from this table to B
      find_path( r.table_name, b, path||a );
    END IF;
  END LOOP;
END;

This is sketchy - it leaves out some of the detail about the SELECT (you know about that) and what the datatype of "path" is - at its simplest it could be just a VARCHAR2 list of tables like 'SHIPS,EQUIPMENT,EQUIPMENT_PARTS,PARTS', but in reality you may want to store more complex information, e.g. the actual JOIN conditions etc.  Also, the implementation of store_path is not specified - could be a temporary table, a PL/SQL table or whatever.

So you would run this process like:

find_path( a => 'SHIPS', b => 'PARTS', path => 'SHIPS,');

And it would generate a list of possible paths.  Note that there could be more than one, each of which could give different results - sometimes legitimate, sometimes perhaps not.  For example, take a simple data model like

CREATE TABLE dept
( deptno PRIMARY KEY
, deptname
, mgr_empno FOREIGN KEY REFERENCES emp
);

CREATE TABLE emp
( empno PRIMARY KEY
, empname
, deptno FOREIGN KEY REFERENCES dept
, supervisor_empno FOREIGN KEY REFERENCES emp
);

I have included a hierarchy ("pig's ear") on emp to make matters worse!  You may have to exclude those altogether otherwise the number of paths is infinite!

What are the paths from DEPT to EMP?
Excluding the emp hierarchy we have:

dept(mgr_empno)->emp(empno)
dept(deptno)->emp(deptno)

You would then have to choose one of these - or present the user with a choice.  They clearly give different results, as they mean different things.  In a more realistic data model I can imagine perhaps dozens of potential join paths, e.g.
dept -> project -> project assignment -> emp
dept -> costcode -> timesheet -> emp
dept -> team -> emp

A different approach that you may want to consider is for you to preconfigure the tables that may be paired and the path to be used.  You may still want more than one valid path for a given pair of tables - e.g.

From    To    Purpose       Query
----    ---   -------       -----
DEPT    EMP   Depts & Emps  dept.deptno = emp.deptno
DEPT    EMP   Depts * Mgrs  dept.mgr_empno = emp.empno
...

This limits the users to "sensible" queries, and ensures that appropriate paths are used.  You could have a maintenance screen to allow someone to add new paths on request.

I hope this has been of some help - it's certainly kept me amused for a while anyway!

hello!

thank you for the reply. appreciate all yr efforts to try and help me out here.

i have managed to do this with a more java-oriented approach.

first, i fill up two hashtables:

masterChildTable: (String parent_table, Vector children)
childMasterTable: (String child_table, Vector parents)
So, the hashtables wud consist of all possible relationships in the DB.

next:
for the x-axis table, i check if the children / parent vectors (call this levelOneVector)  contain the y-axis table. if true, then.. the problem is simple. else, i get the parents & children of all of levelOneVector and repeat the procedure. to make this process a little simpler, i also put the parents/ children of the y-axis table in another vector and check from the other way around as well.

this is meant to be recursive, but then.. it doesnt make sense to repeat it forever. so, i have called the function 4 times. i dont expect the tables in my DB to have FKs from across 4 tables anyway.

this is indeed an expensive approach. this was my idea initially. but i believed tht there shud have been an SQL approach that wud make this simpler. but from yr reply, i gather that there really isnt any quick-fix to this. yr PL/SQL procedure based solution runs parallel to what i have just described using my java-oriented approach.

thanks again for all yr efforts.

--$uDhA

No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

PAQ with points refunded

Please leave any comments here within the next four days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

walterecook
EE Cleanup Volunteer