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.
Expecting result like below -
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