Home » SQL & PL/SQL » SQL & PL/SQL » Query to find common column in more then two tables
Query to find common column in more then two tables [message #669640] Fri, 04 May 2018 12:32 Go to next message
KavitaSSwami
Messages: 7
Registered: May 2018
Junior Member
I am trying to find common column in there tables. Each table contains more then 60 columns, is there any query to find common column in three tables so that i can join
Re: Query to find common column in more then two tables [message #669642 is a reply to message #669640] Fri, 04 May 2018 12:51 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read the OraFAQ Forum Guide and How to use code tags and make your code easier to read

You seem to be describing the ANSI SQL natural join:
orclx>
orclx> select * from emp natural join dept;

    DEPTNO      EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM DNAME          LOC
---------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- -------------- -------------
        10       7782 CLARK      MANAGER         7839 1981-06-09:00:00:00       2450            ACCOUNTING     NEW YORK
        10       7839 KING       PRESIDENT            1981-11-17:00:00:00       5000            ACCOUNTING     NEW YORK
        10       7934 MILLER     CLERK           7782 1982-01-23:00:00:00       1300            ACCOUNTING     NEW YORK
        20       7566 JONES      MANAGER         7839 1981-04-02:00:00:00       2975            RESEARCH       DALLAS
        20       7902 FORD       ANALYST         7566 1981-12-03:00:00:00       3000            RESEARCH       DALLAS
        20       7876 ADAMS      CLERK           7788 1987-05-23:00:00:00       1100            RESEARCH       DALLAS
        20       7369 SMITH      CLERK           7902 1980-12-17:00:00:00        802            RESEARCH       DALLAS
        20       7788 SCOTT      ANALYST         7566 1987-04-19:00:00:00       3000            RESEARCH       DALLAS
        30       7521 WARD       SALESMAN        7698 1981-02-22:00:00:00       1250        500 SALES          CHICAGO
        30       7844 TURNER     SALESMAN        7698 1981-09-08:00:00:00       1500          0 SALES          CHICAGO
        30       7499 ALLEN      SALESMAN        7698 1981-02-20:00:00:00       1600        300 SALES          CHICAGO
        30       7900 JAMES      CLERK           7698 1981-12-03:00:00:00        950            SALES          CHICAGO
        30       7698 BLAKE      MANAGER         7839 1981-05-01:00:00:00       2850            SALES          CHICAGO
        30       7654 MARTIN     SALESMAN        7698 1981-09-28:00:00:00       1250       1400 SALES          CHICAGO

14 rows selected.

orclx>
Re: Query to find common column in more then two tables [message #669643 is a reply to message #669640] Fri, 04 May 2018 12:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

What criteria must be met for any two columns in different tables to be considered "common"?
Must the column_names be 100% identical?
Must the datatype for both columns to be 100% identical?
If one column is STOCK_NAME VARCHAR2(25) & column in different table STOCK_NAME VARCHAR2(30), then are they "common"?
Re: Query to find common column in more then two tables [message #669646 is a reply to message #669642] Fri, 04 May 2018 13:54 Go to previous messageGo to next message
KavitaSSwami
Messages: 7
Registered: May 2018
Junior Member
I am getting all the columns from all three tables , but i need only common column in three tables
Re: Query to find common column in more then two tables [message #669649 is a reply to message #669646] Fri, 04 May 2018 14:21 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Come on, man. Project the columns you need. How am I supposed to know what you want?
Re: Query to find common column in more then two tables [message #669650 is a reply to message #669640] Fri, 04 May 2018 16:02 Go to previous messageGo to next message
JPBoileau
Messages: 88
Registered: September 2017
Member
Sounds like you're trying to figure out your data model and its foreign key relationships.

This will get you started:

SELECT
   C1.TABLE_NAME AS REFERRED_TABLE,
   C1.CONSTRAINT_NAME AS REFERRED_CONSTRAINT,
   CC1.COLUMN_NAME AS REFERRED_COLUMN,
   CC1.POSITION AS REFERRED_POSITION,
   C2.TABLE_NAME AS REFERRING_TABLE,
   C2.CONSTRAINT_NAME AS REFERRING_CONSTRAINT,
   CC2.COLUMN_NAME AS REFERRING_COLUMN,
   CC2.POSITION AS REFERRING_POSITION
FROM
   DBA_CONSTRAINTS C1,
   DBA_CONSTRAINTS C2,
   DBA_CONS_COLUMNS CC1, 
   DBA_CONS_COLUMNS CC2
WHERE
   C2.OWNER = 'MYSCHEMA' AND 
   C1.OWNER = C2.OWNER AND
   C2.CONSTRAINT_TYPE = 'R' AND
   C2.R_CONSTRAINT_NAME = C1.CONSTRAINT_NAME AND
   C1.CONSTRAINT_TYPE = 'P' AND
   CC2.CONSTRAINT_NAME = C2.CONSTRAINT_NAME AND
   CC1.CONSTRAINT_NAME = C1.CONSTRAINT_NAME AND
   CC1.POSITION = CC2.POSITION 
ORDER BY
   CC1.TABLE_NAME,
   CC1.POSITION,
   CC2.TABLE_NAME,
   CC2.POSITION;
   

JP

[Updated on: Fri, 04 May 2018 16:02]

Report message to a moderator

Re: Query to find common column in more then two tables [message #669651 is a reply to message #669646] Fri, 04 May 2018 16:10 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
KavitaSSwami wrote on Fri, 04 May 2018 14:54
I am getting all the columns from all three tables , but i need only common column in three tables
select  column_name,
        listagg(c.table_name,',') within group(order by c.table_name) table_list
  from  user_tab_columns c,
        user_tables t
  where c.table_name = t.table_name
  group by column_name
  having count(*) = 3
/

COLUMN_NAME          TABLE_LIST
-------------------- --------------------------------------------------
CODE                 LIST_PART_TAB,TEST_CLCL,TEST_TAB
DIFF1                TBL1,TBL2,TBL2_TEMP
DNAME                DEPT,DEPT_AND_EMP,DEPT_TBL
LOC                  DEPT,DEPT_AND_EMP,DEPT_TBL
OLD_VALUE            DATA,SYS_EXPORT_SCHEMA_01,SYS_EXPORT_TABLE_01
SERVICE_NAME         SID_TBL,SYS_EXPORT_SCHEMA_01,SYS_EXPORT_TABLE_01
SETID                PS_STAGING_TBL,PS_VENDOR,PS_VENDOR_CNTCT

7 rows selected.

SQL> 

SY.
Re: Query to find common column in more then two tables [message #669652 is a reply to message #669646] Sat, 05 May 2018 01:30 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
KavitaSSwami wrote on Fri, 04 May 2018 19:54
I am getting all the columns from all three tables , but i need only common column in three tables
You can use the trace facility to show the common columns (which may not be join columns, remember) see them listed at the end of this:
orclx>
orclx> set autot trace exp
orclx> select * from employees natural join departments;

Execution Plan
----------------------------------------------------------
Plan hash value: 2052257371

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |    11 |   990 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |             |    11 |   990 |     6   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| DEPARTMENTS |    11 |   231 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMPLOYEES   |   107 |  7383 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID"
              AND "EMPLOYEES"."MANAGER_ID"="DEPARTMENTS"."MANAGER_ID")
Re: Query to find common column in more then two tables [message #669700 is a reply to message #669652] Tue, 08 May 2018 08:45 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
The following code will show you the column names in common between the two tables

SELECT COLUMN_NAME
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'TABLE1
INTERSECT
SELECT COLUMN_NAME
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'TABLE2';
Re: Query to find common column in more then two tables [message #669710 is a reply to message #669700] Wed, 09 May 2018 01:38 Go to previous messageGo to next message
quirks
Messages: 82
Registered: October 2014
Member
I'd try it that way:
SELECT T1.OWNER, T1.TABLE_NAME, T1.COLUMN_NAME, T2.COLUMN_NAME, T2.TABLE_NAME
  FROM ALL_TAB_COLUMNS T1, ALL_TAB_COLUMNS T2
 WHERE T1.OWNER = T2.OWNER
   AND T1.COLUMN_NAME = T2.COLUMN_NAME
   AND T1.TABLE_NAME != T2.TABLE_NAME
   AND T1.OWNER = '<put your schema name in here>'
Re: Query to find common column in more then two tables [message #669717 is a reply to message #669646] Wed, 09 May 2018 06:44 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
KavitaSSwami wrote on Fri, 04 May 2018 13:54
I am getting all the columns from all three tables , but i need only common column in three tables
Then answer BlackSwan's question and tell us how YOU define "common column".
Re: Query to find common column in more then two tables [message #669718 is a reply to message #669717] Wed, 09 May 2018 06:48 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
OP has not visited since original posting date.
>Last Visited: Fri, 04 May 2018 14:02
Re: Query to find common column in more then two tables [message #669810 is a reply to message #669717] Mon, 14 May 2018 11:44 Go to previous messageGo to next message
KavitaSSwami
Messages: 7
Registered: May 2018
Junior Member
EdStevens wrote on Wed, 09 May 2018 06:44
KavitaSSwami wrote on Fri, 04 May 2018 13:54
I am getting all the columns from all three tables , but i need only common column in three tables
Then answer BlackSwan's question and tell us how YOU define "common column".
common column as BEGIN_date from t1 and BEGIN_date from t2
Re: Query to find common column in more then two tables [message #669811 is a reply to message #669810] Mon, 14 May 2018 12:01 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/mv/msg/204650/669700/#msg_669700
Previous Topic: SELECT WITH TIMESTAMP AND SYSDATE
Next Topic: How to set the earliest date
Goto Forum:
  


Current Time: Thu Mar 28 14:11:28 CDT 2024