How to compare different values within the same column-CodePudding

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(2));
TID  contract_type
------------------
1       P
1       S
1       P
2       S
2       S
3       P
3       S
4       S

I am looking EID which is having contract type is 'S' in type table. (or emp table with effective date is greater than sysdate and in the type table with only contract_type ='S')

Actual result :
2
4

My query is not giving the correct results.

select emp.EID
 from emp,type
 where EID = TID
         contract_type ='S'
         effective_date >= sysdate
         group by TID 
         having count(TID) >= 1;

CodePudding user response：

I would use exists logic here:

SELECT EID
FROM EMP e
WHERE effective_date >= SYSDATE AND
      NOT EXISTS (
          SELECT 1
          FROM "type" t
          WHERE t.TID = e.EID AND
                t.contract_type <> 'S'
      );

CodePudding user response：

If you want to keep your idea with COUNT and GROUP BY, you should count other contract types than the 'S' ones and check this is 0:

SELECT e.eid
FROM emp e 
JOIN type t ON e.eid = t.tid
WHERE 
e.effective_date >= sysdate
GROUP BY e.eid 
HAVING COUNT(CASE WHEN t.contract_type <> 'S' THEN 1 END) = 0;

This query will return 2 and 4 for your sample data.

Try out: db<>fiddle

Another option is as already said here using NOT EXISTS.

Actual result : 2 4

Actual result :
2
4