Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Joining multiple table to get unique records

anand_gpDec 30 2018 — edited Dec 31 2018

Hi,

I was trying to get merged result of multiple tables having a common ID field which joins these tables. However, I am not able to get the result what I was expecting. Trying to show an example using below sample (and they are nothing to do with employee and department table concept). Assume three tables, T1, T2 and T3 having columns NAME, DEPT & WORK respectively. And all 3 tables having ID column which can be joined and there are non-joining records at every table. They are not master - detail tables so, different set of records exists in these tables and there are matching records as well. I have taken minimum set of combination of records, which are common across tables and also specific to each table. The one marked in yellow color are matching records between tables with different combination.

pastedImage_1.png

Expecting result like below -

pastedImage_5.png

Can someone help me on this. Thanks.

Below is what I tried -

CREATE TABLE T1(ID NUMBER, NAME VARCHAR2(10));

INSERT INTO T1 VALUES (1, 'SAM');

INSERT INTO T1 VALUES (2,'JOHN');

INSERT INTO T1 VALUES (7,'PETER');

INSERT INTO T1 VALUES (5,'JOE');

CREATE TABLE T2(ID NUMBER, DEPT VARCHAR2(10));

INSERT INTO T2 VALUES (2,'ADMIN');

INSERT INTO T2 VALUES (7,'PUR');

INSERT INTO T2 VALUES (3,'FIN');

INSERT INTO T2 VALUES (6,'SALES');

CREATE TABLE T3(ID NUMBER, WORK VARCHAR2(10));

INSERT INTO T3 VALUES (2,'REGISTER');

INSERT INTO T3 VALUES (3,'CALCULATE');

INSERT INTO T3 VALUES (5,'DRAWING');

INSERT INTO T3 VALUES (4,'MARKING');

I tried with below query, but getting duplicate records for ID 3 & 5.

SELECT * FROM T1 A LEFT JOIN T2 B USING (ID) LEFT JOIN T3 C USING (ID)

UNION

SELECT * FROM T1 A LEFT JOIN T2 B USING (ID) RIGHT JOIN T3 C USING (ID)

UNION

SELECT * FROM T1 A RIGHT JOIN T2 B USING (ID) LEFT JOIN T3 C USING (ID)

UNION

SELECT * FROM T1 A RIGHT JOIN T2 B USING (ID) RIGHT JOIN T3 C USING (ID)

UNION

SELECT * FROM T1 A JOIN T2 B USING (ID) JOIN T3 C USING (ID)

Regards,

-Anand

This post has been answered by Frank Kulash on Dec 30 2018
Jump to Answer
Comments
Post Details
Added on Dec 30 2018
4 comments
5,911 views