I have 2 different sets of tables with 2 common columns.

Table 1 : STUDENT

TABLE 2 : MAPPING

Now, I am trying to find all details of table STUDENT which are not present table MAPPING .

Example

I tried using Minus but it works if columns are same .

Any help will be great .

Regards,

Abhay

>Solution :

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>