|
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 |
John Watson
Messages: 8935 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 #669650 is a reply to message #669640] |
Fri, 04 May 2018 16:02 |
|
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 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
KavitaSSwami wrote on Fri, 04 May 2018 14:54I 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 |
John Watson
Messages: 8935 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
KavitaSSwami wrote on Fri, 04 May 2018 19:54I 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")
|
|
|
|
|
|
|
|
|