I have 2 tables TABLE1 with column names ROLLNO and STATUS & TABLE2 with column names ROLL and STATUS.
CREATE TABLE TABLE1 ( ROLLNO VARCHAR2(10) NOT NULL, STATUS VARCHAR2(2));
INSERT INTO TABLE1 VALUES ('1234567890', '01');
INSERT INTO TABLE1 VALUES ('1234567891', '02');
INSERT INTO TABLE1 VALUES ('1234567895', '01');
INSERT INTO TABLE1 VALUES ('1234567896', '02');
INSERT INTO TABLE1 VALUES ('1234567897', '00');
------------------------------------------------------------------------
CREATE TABLE TABLE2 ( ROLLNO VARCHAR2(10) NOT NULL, STATUS VARCHAR2(2));
INSERT INTO TABLE2 VALUES ('1234567890', '01');
INSERT INTO TABLE2 VALUES ('1234567891', '02');
INSERT INTO TABLE2 VALUES ('1234567892', '01');
INSERT INTO TABLE2 VALUES ('1234567893', '02');
INSERT INTO TABLE2 VALUES ('1234567894', '03');
INSERT INTO TABLE2 VALUES ('1234567898', '04');
---------------------------------------------------------------------------
I just want to get the output as shown below:
ROLLNO STATUS
-----------------------
1234567890 01
1234567891 02
1234567892 01
1234567893 02
1234567894 03
1234567895 01
1234567896 02
We need to select the rows having status 01, 02, 03 from table2 and rows from table1 having status 01,02 and If the rows of table1 having status 01, 02 are present in table2, then we shouldn't enter duplicate rows in result table
there should be unique rows in the result.
I tried using this query but this query is wrong for this problem
SELECT DISTINCT * FROM
(
SELECT T2.ROLLNO, T2.STATUS FROM TABLE2 T2
WHERE T2.STATUS IN ('01','02','03')
UNION ALL
SELECT T1.ROLLNO, T1.STATUS FROM TABLE1 T1
WHERE T1.STATUS IN ('01','02')
);
Is this correct query?
Maybe something like this?
SQL> select *
2 from (
3 select
4 x.*,
5 row_number() over ( partition by rollno order by tab, status ) as x
6 from (
7 select t.*, 'T1' tab from table1 t
8 where status in ('01','02','03')
9 union all
10 select t.*, 'T2' tab from table2 t
11 where status in ('01','02','03')
12 ) x
13 )
14 where x = 1;
ROLLNO ST TA X
---------- -- -- ------------------------------
1234567890 01 T1 1
1234567891 02 T1 1
1234567892 01 T2 1
1234567893 02 T2 1
1234567894 03 T2 1
1234567895 01 T1 1
1234567896 02 T1 1