I have 2 tables table_a and table_b with below structure
Table_A
emp_id number
dept number
Table_b
Keep_id number
Discard_id number
Dept_no number
table_a
emp_id dept_no
101 10
102 10
103 20
108 10
107 20
table_b
keep_id discard_id dept_no
101 108 10
107 103 20
My question is
how to get all emp_id from table_A which are present in keep_id and discard_id columns of Table_B of same Dept_no.
create table table_a(emp_id number, dept_no number);
insert into test_a values(102,10);
insert into test_a values(103,20);
insert into test_a values(107,20);
insert into test_a values(108,10);
insert into test_a values(119,30);
commit;
create table test_b(keep_empid number, dicard_empid, dept_no);
insert into test_b values(101,108,10);
insert into test_b values(103,107,20);
insert into test_b values(110,187,10);
commit;
The result should be like below
emp_id,dept_id
101,10
108,10
103,20
107,20
Please test your scripts before posting them! Those "create tables" don't work...
And what precisely are you looking for?
Values from test_a with emp_ids equal to the keep/discard values for the department?
Or those between the keep/discard values for the department?
Whichever it is, you just need to join:
create table test_a(emp_id number, dept_no number);
insert into test_a values(102,10);
insert into test_a values(103,20);
insert into test_a values(107,20);
insert into test_a values(108,10);
insert into test_a values(119,30);
commit;
create table test_b(keep_empid number, dicard_empid int, dept_no int);
insert into test_b values(101,108,10);
insert into test_b values(103,107,20);
insert into test_b values(110,187,10);
commit;
select a.* from test_a a
join test_b b
on b.dept_no = a.dept_no
and (a.EMP_ID = b.keep_empid or a.emp_id = dicard_empid)
order by 2, 1;
EMP_ID DEPT_NO
108 10
103 20
107 20
select a.* from test_a a
join test_b b
on b.dept_no = a.dept_no
and a.EMP_ID between b.keep_empid and dicard_empid
order by 2, 1;
EMP_ID DEPT_NO
102 10
108 10
103 20
107 20