CODEWITHSUNDEEP
sqloracle-database
alex.roia
alex.roia

Reputation: 15

Select Minus with fixed column in Oracle sql

I'd like to do a MINUS operation between two tables like these:

table1:

employee_id |   job  | sector
----------- | ------ | ------
10          | a      | 1
10          | a      | 2
10          | b      | 4

table2:

job  | sector
---- | ------
a    | 1
a    | 2
a    | 3
b    | 1
b    | 4
c    | 1
c    | 2

and as result I want, for every employee_id, the {job,sector} not connected in table1.

RESULT:

employee_id | job | sector
----------- | --- | ------
10          | a   | 3
10          | b   | 1
10          | c   | 1
10          | c   | 2

Is it possible?

I hope I have written in a clear way! Thanks!

Upvotes: 1

Views: 98

Answers (4)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

First select the complete data set, i.e. employee_id X job/sector. From these remove the existing table1 entries to get the lacking ones. (I've renamed your table table2 to job_sector for readability. I also suppose you have an employee table.)

select e.employee_id, js.job, js.sector
from employee e
cross join job_sector js
minus
select employee_id, job, sector
from table1;

Upvotes: 1

haMzox
haMzox

Reputation: 2109

You can simply achieve it by doing a left join. You query will look something like this:

SELECT T2.*
FROM TABLE2 T2 LEFT JOIN
     TABLE1 T1
     ON T2.JOB = T1.JOB AND T2.SECTOR = T2.SECTOR
WHERE T1.JOB IS NULL;

Though table must be linked in order to get the employee id.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

This sounds like just a left join (or not in or not exists):

select 10 as employee_id, t2.*
from table2 t2 left join
     table1 t1
     on t2.job = t1.job and t2.sector = t1.sector
where t1.job is null;

I am a bit confused on how you get the employee id if the tables are not linked.

If you have multiple employees in t1, then you can do:

select e.employee_id, t2.*
from (select distinct employee_id from t1) e cross join
     table2 t2 left join
     table1 t1
     on t2.job = t1.job and t2.sector = t1.sector and
        e.employee_id = t1.employee_id
where t1.job is null;

Upvotes: 0

JohnHC
JohnHC

Reputation: 11195

Left join, where t2 is null

select t1.*
from table1 t1
let join table2 t2
  on t1.job = t2.job
  and t1.sector = t2.sector
where t2.job is null

Upvotes: 0

Related Questions