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

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

CodePudding user response：

SELECT s.SNAME, s.JOB
FROM staff s
LEFT JOIN staff b ON s.JOB = b.JOB 
WHERE b.SNAME = 'JONES' AND s.SNAME != 'JONES'

CodePudding user response：

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:

SNAME	JOB
HAYAT	MANAGER
CLARK	MANAGER

fiddle