Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

how can i get the link attributes between two tables

Posted on 2003-03-19
6
Medium Priority
?
660 Views
Last Modified: 2010-08-05
hi pple

i need to find out HOW two tables are related.

sample tables:

SHIPS ( HULL_ID [PK] , VESSEL_NAME, GROSS_TONNAGE )

EQUIPMENT ( E_ID [PK] , HULL_ID [FK frm SHIPS], DESC )

EQUIPMENT_PARTS ( E_ID [PK, FK frm Equipment], PART_ID [PK, FK frm PARTS )

PARTS ( PART_ID [PK] , E_ID [PK, FK frm EQUIPMENT_PARTS], MANUFACTURER_ID)


==> SHIPS and PARTS are related thro' EQUIPMENT and EQUIPMENT_PARTS
i need to get this relationship:

Ships --> Equipment (E_ID) --> Equipment_Parts ( Parts_ID) --> PARTS

currently, i can get the direct parent and child tables of a given table using:

SELECT lpad ( ' ', 2 * ( LEVEL - 1 ) ) || table_name as Child_Tables
FROM ALL_CONSTRAINTS
START WITH R_CONSTRAINT_NAME in
(
  select constraint_name
  from all_constraints
  where table_name = '%table_name%'
  and constraint_type = 'P'
)
CONNECT BY PRIOR CONSTRAINT_NAME = R_CONSTRAINT_NAME ;


To get ALL parents:

SELECT lpad ( ' ', 2 * ( LEVEL - 1 ) ) || table_name as Parent_Tables
FROM ALL_CONSTRAINTS
where owner = 'CHARTUSER'
START WITH CONSTRAINT_NAME in
(
  select constraint_name
  from all_constraints
  where table_name = '%table_name%'
  and constraint_type = 'R'
)
CONNECT BY PRIOR R_CONSTRAINT_NAME = CONSTRAINT_NAME ;

what i need to do is:

1) get children & parents of SHIPS

2) for each element of (1), get children, parents till i get PARTS

3) so, i will get EQUIPMENT, EQUIPMENT_PARTS and then, PARTS.

this wud be very expensive. my appln is in Java and i will need to call these functions - that makes it more complex. is there any simpler solution using only SQL, PL/SQL?

my main problem is to find out WHAT attributes line two tables. this wudnt be a big deal if it were just simple parent-child tables. but, how do i find out the attributes that link PARTS and SHIPS table? i need this if i want to write a query to get: which part belongs to which ship.

any ideas? -please help. i have been struck with this problem for days now without any luck...

thanks in advance

--$uDhA

0
Comment
Question by:s4sudha
6 Comments
 
LVL 15

Expert Comment

by:andrewst
ID: 8169330
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;
0
 

Author Comment

by:s4sudha
ID: 8170124

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
0
 
LVL 15

Expert Comment

by:andrewst
ID: 8173019
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!


0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:s4sudha
ID: 8187221
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
0
 
LVL 17

Expert Comment

by:walterecook
ID: 10348397
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
0
 

Accepted Solution

by:
PashaMod earned 0 total points
ID: 10375053
PAQed, with points refunded (50)

PashaMod
Community Support Moderator
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question