On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Thursday, February 19, 2009

How to Find a Linking Table

What do you do if you need to select data from two tables, but discover they have no common column? You look for the table that links them.

As an example, I'm using the Oracle-supplied HR schema. This query will find the table (if there is one) that has a field in Locations, and also one in Regions. (This is based on column names so if a column was named 'RegID', it would not match to 'Region_ID' even if the data was the same.)
SQL> desc locations
Name Null? Type
----------------- -------- ------------
LOCATION_ID NOT NULL NUMBER(4)
STREET_ADDRESS VARCHAR2(40)
POSTAL_CODE VARCHAR2(12)
CITY NOT NULL VARCHAR2(30)
STATE_PROVINCE VARCHAR2(25)
COUNTRY_ID CHAR(2)

SQL> desc regions
Name Null? Type
----------------- -------- ------------
REGION_ID NOT NULL NUMBER
REGION_NAME VARCHAR2(25)
When the query is run, the user supplies two table names. The query then gets a list of available column names, and searches User_Tab_Columns to find other tables with columns in that list.
Set Newpage 1 Pagesize 50 Linesize 200 Echo &Onoff Feedback &Onoff Verify &Onoff

Accept Table1 Prompt '1st Table: '
Accept Table2 Prompt '2nd Table: '

Column Table_Name Heading 'Tables From|Which Data|Is Needed'
Column Connecting_Table Heading 'Table Through|Which Connection|Needs To Be Made'
Column Column_Name Heading 'Connecting|Column_Name'

--+-----------------------------------------------------------------------------
--| Get a list of columns in either table
--+-----------------------------------------------------------------------------
With Available_Columns As
(
Select Table_Name
, Column_Name
From User_Tab_Columns
Where Table_Name In ( Upper('&Table1')
, Upper('&Table2')
)
)
--+-----------------------------------------------------------------------------
--| Get the final list of original table names, connecting tables, and their
--| columns.
--+-----------------------------------------------------------------------------
Select Table_Name
, Connecting_Table
, Column_Name
From
--+---------------------------------------------------------------------
--| We need to count them up because if it doesn't have at least 2
--| columns (Col_Count > 1), or they're both in the same table
--| (Tbl_Count = 1), it wouldn't be a usable set.
--|
--| Table_Name Column_Name Connecting_Table Col_Count Tbl_Count
--| ---------- ----------- ---------------- --------- ---------
--| LOCATIONS COUNTRY_ID COUNTRIES 2 1
--| REGIONS REGION_ID COUNTRIES 2 1
--| LOCATIONS LOCATION_ID DEPARTMENTS 1 1
--+---------------------------------------------------------------------
(
Select Table_Name
, ac.Column_Name
, Connecting_Table
, Count(*) Over (Partition By Connecting_Table)
As
Col_Count
, Count(*) Over (Partition By Connecting_Table,
Table_Name)
As
Tbl_Count
From Available_Columns ac,
--+---------------------------------------------------------------
--| Get a list of columns from other tables that match those found
--| in the two tables requested.
--|
--| Connecting_Table Column_Name
--| ------------------ -----------
--| COUNTRIES COUNTRY_ID
--| COUNTRIES REGION_ID
--| DEPARTMENTS LOCATION_ID
--+---------------------------------------------------------------
(
Select Table_Name
As
Connecting_Table
, Column_Name
From User_Tab_Columns
Where Table_Name Not In ( Upper('&Table1')
, Upper('&Table2')
)
And Table_Name Not Like 'BIN$%' /* not in Recycle Bin */
And Column_Name In
(
Select Column_Name
From Available_Columns
)
)T2
--+---------------------------------------------------------------
Where ac.Column_Name = T2.Column_Name
)
Where Col_Count > 1
And Tbl_Count = 1;

Undefine OnOff Table1 Table2

No comments:

Post a Comment