Oracle SQL query SELECT with IN not correctly returning the result-CodePudding

I have an Oracle query using SELECT and IN.

Below is the table EMPLOYEE data:

INSERT INTO EMPLOYEES (BADGEID, EMP_NAME) VALUES ('10001', 'John');
INSERT INTO EMPLOYEES (BADGEID, EMP_NAME) VALUES ('10002', 'Mark');
INSERT INTO EMPLOYEES (BADGEID, EMP_NAME) VALUES ('10003', 'James');

This one is WRISTRAP data:

INSERT INTO WRISTRAP (BADGEID_FK, WRISTRAP, FOOT, DATEADDED) 
VALUES ('10001', 'Pass', 'Pass', TO_DATE('13-OCT-2022 13:10:12', 'dd-MON-yyyy hh24:mi:ss'));
INSERT INTO WRISTRAP (BADGEID_FK, WRISTRAP, FOOT, DATEADDED) 
VALUES ('10001', 'Fail', 'Pass', TO_DATE('13-OCT-2022 13:09:34', 'dd-MON-yyyy hh24:mi:ss'));
INSERT INTO WRISTRAP (BADGEID_FK, WRISTRAP, FOOT, DATEADDED) 
VALUES ('10001', 'Pass', 'Pass', TO_DATE('13-OCT-2022 13:08:17', 'dd-MON-yyyy hh24:mi:ss'));
INSERT INTO WRISTRAP (BADGEID_FK, WRISTRAP, FOOT, DATEADDED) 
VALUES('10002', 'Pass', 'Pass', TO_DATE('13-OCT-2022 13:16:22', 'dd-MON-yyyy hh24:mi:ss'));
INSERT INTO WRISTRAP (BADGEID_FK, WRISTRAP, FOOT, DATEADDED) 
VALUES ('10002', 'Pass', 'Pass', TO_DATE('13-OCT-2022 13:11:25', 'dd-MON-yyyy hh24:mi:ss'));
INSERT INTO WRISTRAP (BADGEID_FK, WRISTRAP, FOOT, DATEADDED) 
VALUES ('10003', 'Pass', 'Fail', TO_DATE('13-OCT-2022 13:21:44', 'dd-MON-yyyy hh24:mi:ss'));

Now I tried to make query to check employee already Wristrap but still get WRISTRAP fail or FOOT fail.

Above data explain that Badge ID 10001 already Wristrap with latest result is All Pass.

This is my SQL query:

SELECT
    E.BADGEID
FROM
    EMPLOYEES E
WHERE
    BADGEID IN (SELECT BADGEID_FK
                FROM WRISTRAP
                WHERE WRISTRAP = 'Fail' OR FOOT = 'Fail')

After execute the query, I still got Badge ID 10001 shown on query data. It should be not there because the latest Wristrap is Pass (Date: 13-OCT-2022 13:10:12).

I also prepare for SQL Fiddle for the demo.

CodePudding user response：

One way to get the values of the last row per badgeid_fk is to group by it and use Oracle's KEEP LAST:

SELECT e.badgeid
FROM employees e
WHERE e.badgeid IN 
(
  SELECT badgeid_fk
  FROM wristrap
  GROUP BY badgeid_fk
  HAVING MAX(wristrap) KEEP (DENSE_RANK LAST ORDER BY dateadded) = 'Fail'
      OR MAX(foot) KEEP (DENSE_RANK LAST ORDER BY dateadded) = 'Fail'
);

One standard-compliant approach would be to use a window function:

WITH latest_wristraps AS
(
  SELECT badgeid_fk, wristrap, foot
  FROM wristrap
  ORDER BY DENSE_RANK() OVER (PARTITION BY badgeid_fk ORDER BY dateadded DESC)
  FETCH FIRST ROW WITH TIES
)
SELECT e.badgeid
FROM employees e
WHERE e.badgeid IN 
(
  SELECT badgeid_fk
  FROM lastest_wristraps
  WHERE wristrap = 'Fail'
  OR foot = 'Fail'
);

CodePudding user response：

I think you should first need to find the latest added date for each BADGE ID and then search for the failed ones. Something like this one should do the trick

 select EMP_NAME, BADGEID from EMPLOYEES,  (
select
  BADGEID_FK,
  WRISTRAP,
  FOOT
FROM
(
  SELECT
    BADGEID_FK,
    WRISTRAP,
    FOOT,
    DATEADDED,
    MAX(DATEADDED) OVER(PARTITION BY BADGEID_FK) as LAST_ADDED_DATE
  FROM
    WRISTRAP  
)
WHERE
  DATEADDED=LAST_ADDED_DATE  )BADGES WHERE   EMPLOYEES.BADGEID=BADGES.BADGEID_FK  AND (BADGES.WRISTRAP = 'Fail' OR BADGES.FOOT = 'Fail')