I have 2 different sets of tables with 2 common columns.
Table 1 : STUDENT
NAME | ID | DEPTID | YEAR | BATCH |
---|---|---|---|---|
abc | 123 | 987 | 2020 | 2 |
mno | 234 | 987 | 2020 | 2 |
abc | 123 | 765 | 2020 | 2 |
zyz | 124 | 765 | 2021 | 1 |
abc | 123 | 986 | 2020 | 2 |
TABLE 2 : MAPPING
ID | DEPTID |
---|---|
123 | 765 |
234 | 987 |
123 | 986 |
Now, I am trying to find all details of table STUDENT which are not present table MAPPING .
Example
NAME | ID | DEPTID | YEAR | BATCH | MISSING |
---|---|---|---|---|---|
abc | 123 | 987 | 2020 | 2 | YES |
zyz | 124 | 765 | 2021 | 1 | YES |
I tried using Minus but it works if columns are same .
Any help will be great .
Regards,
Abhay
CodePudding user response:
Looks like an ordinary NOT EXISTS
. Sample data till line #13; query begins at line #14.
SQL> with
2 student (name, id, deptid, year, batch) as
3 (select 'abc', 123, 987, 2020, 2 from dual union all
4 select 'mno', 234, 987, 2020, 2 from dual union all
5 select 'abc', 123, 765, 2020, 2 from dual union all
6 select 'zyz', 124, 765, 2021, 1 from dual union all
7 select 'abc', 123, 986, 2020, 2 from dual
8 ),
9 mapping (id, deptid) as
10 (select 123, 765 from dual union all
11 select 234, 987 from dual union all
12 select 123, 986 from dual
13 )
14 select s.name, s.id, s.deptid, s.year, s.batch, 'YES' missing
15 from student s
16 where not exists (select null
17 from mapping m
18 where m.id = s.id
19 and m.deptid = s.deptid
20 )
21 order by s.name;
NAM ID DEPTID YEAR BATCH MIS
--- ---------- ---------- ---------- ---------- ---
abc 123 987 2020 2 YES
zyz 124 765 2021 1 YES
SQL>