title: How do i determine the related column between tables?
body: <p>I have two tables. I need to find the column(s) that relate them. There are not any foreign keys. The primary key for 'CUSTTABLE' is 'ACCOUNTNUM' and for 'DIRPARTYTABLE' is 'RECID'. The related columns are 'CUSTTABLE.PARTY' and 'DIRPARTYTABLE.RECID'. I need this to return 'CUSTTABLE.ACCOUNTNUM', 'DIRPARTYTABLE.NAME', 'DIRPARTYTABLE.PARTYNUMBER' on the same line. The code I have returns column names that match. However, the names are often never the same in my case.</p>
  
  <p>Is there a similar way to return the columns with matching values, not names? even if it returns multiple columns that would help. In reality, these tables have 100+ columns and I have many tables to determine the same type of relationship.</p>
  
  <p>CUSTTABLE:</p>
  
  <pre><code>ACCOUNTNUM     CUSTGROUP   PARTY
  
  305342         LTL         5637459693
  305343         LTL         5637468513
  305345         LTL         5637472531
  305398         LTL         5637468514
  305405         LTL         5637468515
  </code></pre>
  
  <p>DIRPARTYTABLE:</p>
  
  <pre><code>NAME                PARTYNUMBER   RECID
  
  ZEP MFG             1500121      5637459693
  TABER EXTRUSIONS    1500122      5637459694
  LAWSON PRODUCTS     1500123      5637459695
  KIMRAY              1500124      5637459696
  ANCHOR PAINT MFG    1500125      5637459697
  </code></pre>
  
  <p>RESULT:</p>
  
  <pre><code>ACCOUNTNUM    NAME        PARTYNUMBER
  305342        ZEP MFG     1500121
  </code></pre>
  
  <p>Query:</p>
  
  <pre><code>select A.COLUMN_NAME
  from INFORMATION_SCHEMA.COLUMNS A
  join INFORMATION_SCHEMA.COLUMNS B
    on A.COLUMN_NAME = B.COLUMN_NAME
  where A.TABLE_NAME = 'CUSTTABLE'
    and B.TABLE_NAME = 'DIRPARTYTABLE'
  </code></pre>