Hi ,
I having two tables emp and type.
create table EMP(ID number(10), effective_date date);
EID Effective_date
--------------------
1 02/14/2023
2 02/15/2023
3 04/30/2023
4 03/24/2023
create table type(ID number(10),contract_type varchar2(1));
TID contract_type
-----------------------
1 P
1 S
1 P
2 S
2 S
3 P
3 S
4 S
I am looking for EID which is having contract type is 'S' in type table. (or emp table with effective date is greater than sysdate and only contract_type ='S' in the type table)
Actual result :
2
4
--------------------------------
My query is not giving the correct results.
select EID
from emp,type
where EID = TID
contract_type ='S'
effective_date >= sysdate
group by TID
having count(TID) >= 1;