By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE staff (sname, job) AS
SELECT 'JONES', 'MANAGER' FROM DUAL UNION ALL
SELECT 'HAYAT', 'MANAGER' FROM DUAL UNION ALL
SELECT 'CLARK', 'MANAGER' FROM DUAL UNION ALL
SELECT 'ALICE', 'CEO' FROM DUAL UNION ALL
SELECT 'BERYL', 'CTO' FROM DUAL
5 rows affected
SELECT SNAME,
JOB
FROM STAFF
WHERE JOB IN (SELECT JOB FROM STAFF WHERE SNAME = 'JONES')
AND sname != 'JONES';
| SNAME | JOB |
|---|---|
| HAYAT | MANAGER |
| CLARK | MANAGER |
SELECT sname,
job
FROM (
SELECT sname,
job,
COUNT(CASE sname WHEN 'JONES' THEN 1 END) OVER (PARTITION BY job)
AS num_jones
FROM staff
)
WHERE num_jones > 0
AND sname != 'JONES';
| SNAME | JOB |
|---|---|
| HAYAT | MANAGER |
| CLARK | MANAGER |