?
Solved

how can i get the link attributes between two tables

Posted on 2003-03-19
6
Medium Priority
?
654 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this article, we’ll look at how to deploy ProxySQL.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

770 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