List the name and job of staff who have the same job as JONES, but do not display the JONES record

This is my query so far, struggling with how not to display the JONES record though…

SELECT SNAME, JOB FROM STAFF
WHERE JOB IN (SELECT JOB FROM STAFF WHERE SNAME = ‘JONES’);

Result…

SNAME JOB

JONES MANAGER
HAYAT MANAGER
CLARK MANAGER

>Solution :

Add another filter to exclude the JONES row(s):

SELECT SNAME,
       JOB
FROM   STAFF
WHERE  JOB IN (SELECT JOB FROM STAFF WHERE SNAME = 'JONES')
AND    sname != 'JONES';

or, without querying the table twice:

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';

Which, for the sample data:

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

Both output:

fiddle