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