Toon DinDarkDevil Toon DinDarkDevil - 2 months ago 7
SQL Question

SQL query oracle select where out


I will tell someone only -> If you can't help me , You shouldn't vote down to my question.


I have problem about....

Data (in table) :


A_ID EMP_ID
1 0123
2 0123
1 0122
1 0121


And Input :


EMP_ID
0123
0124
0125
0121
0120


I need output : (out put
EMP_ID
not in my table)


EMP_ID
0124
0125
0120



FYI : I can't create table on this schema. (My permission is denied)


Now, I have no idea. Can someone help me solve please ?

Answer

OK !

At first modified data input to SQL. (may be use "Text Editor" for help)

EMP_ID
0123
0124
0125
0121
0120

Result

SELECT '0123' AS EMP_ID FROM DUAL UNION
  SELECT '0124' AS EMP_ID FROM DUAL UNION
  SELECT '0125' AS EMP_ID FROM DUAL UNION
  SELECT '0121' AS EMP_ID FROM DUAL UNION
  SELECT '0120' AS EMP_ID FROM DUAL 

Next use SQL "not in" where cause.

CREATE TABLE "POLICY"."A_EMP" (
  A_ID NUMBER(5) PRIMARY KEY,
  EMP_ID NUMBER(4) NOT NULL
);

INSERT INTO "POLICY"."A_EMP" (A_ID, EMP_ID) VALUES ('1', '0123');
INSERT INTO "POLICY"."A_EMP" (A_ID, EMP_ID) VALUES ('2', '0123');
INSERT INTO "POLICY"."A_EMP" (A_ID, EMP_ID) VALUES ('1', '0122');
INSERT INTO "POLICY"."A_EMP" (A_ID, EMP_ID) VALUES ('1', '0121');

-- ### Try this ! ### ---
SELECT T.EMP_ID FROM (  
  SELECT '0123' AS EMP_ID FROM DUAL UNION
  SELECT '0124' AS EMP_ID FROM DUAL UNION
  SELECT '0125' AS EMP_ID FROM DUAL UNION
  SELECT '0121' AS EMP_ID FROM DUAL UNION
  SELECT '0120' AS EMP_ID FROM DUAL 
) T WHERE T.EMP_ID NOT IN (SELECT B.EMP_ID FROM A_EMP B);