Hello ...
I have a question regarding comparing values in one table’s column with another table’s column. For some reason, this logic always seems to evade me.
Here are my two tables:
[tt]TABLE A TABLE B
DIR_NM CHAR 15 USER_NM CHAR 50
EMP_NUM CHAR 17 AVID CHAR 10
AVID CHAR 10 LOC_ID CHAR 1
LST_NM CHAR 30 APPRVR_IND CHAR 1
FRST_NM CHAR 50 CHG_DT_IND CHAR 1
MID_INIT CHAR 8 BII_IND CHAR 1
DSPY_NM CHAR 50 VSS_IND CHAR 1
TITLE CHAR 55 VTM_ONLY_IND CHAR 1
DEPT_NM CHAR 45 REQ_ADD_BY CHAR 20
MGR_ID CHAR 50 REQ_CHG_BY CHAR 20
CCID CHAR 13 EXP_DT DATE 4
ORG CHAR 50 CMNT CHAR 200
DEPT_ID CHAR 10 REC_ADD_ID CHAR 8
CORP_CD CHAR 3 REC_ADD_TS TIMESTMP 10
EVP_CD CHAR 5 REC_CHG_ID CHAR 8[/tt]
The common columns between the two fields is the AVID column.
Now, [tt]TABLE A[/tt] is a table populated by the master employee database, and [tt]TABLE B[/tt] is an application table with user data. I want to find out what users in [tt]TABLE B[/tt] no longer exist in [tt]TABLE A[/tt] (are no longer with the company).
Here is the SQL I tried:
However, the result set produced no output. I don’t know if that’s because there is no output to produce, or if the logic is wrong.
Thanks for the help ...
Kent
I have a question regarding comparing values in one table’s column with another table’s column. For some reason, this logic always seems to evade me.
Here are my two tables:
[tt]TABLE A TABLE B
DIR_NM CHAR 15 USER_NM CHAR 50
EMP_NUM CHAR 17 AVID CHAR 10
AVID CHAR 10 LOC_ID CHAR 1
LST_NM CHAR 30 APPRVR_IND CHAR 1
FRST_NM CHAR 50 CHG_DT_IND CHAR 1
MID_INIT CHAR 8 BII_IND CHAR 1
DSPY_NM CHAR 50 VSS_IND CHAR 1
TITLE CHAR 55 VTM_ONLY_IND CHAR 1
DEPT_NM CHAR 45 REQ_ADD_BY CHAR 20
MGR_ID CHAR 50 REQ_CHG_BY CHAR 20
CCID CHAR 13 EXP_DT DATE 4
ORG CHAR 50 CMNT CHAR 200
DEPT_ID CHAR 10 REC_ADD_ID CHAR 8
CORP_CD CHAR 3 REC_ADD_TS TIMESTMP 10
EVP_CD CHAR 5 REC_CHG_ID CHAR 8[/tt]
The common columns between the two fields is the AVID column.
Now, [tt]TABLE A[/tt] is a table populated by the master employee database, and [tt]TABLE B[/tt] is an application table with user data. I want to find out what users in [tt]TABLE B[/tt] no longer exist in [tt]TABLE A[/tt] (are no longer with the company).
Here is the SQL I tried:
Code:
SELECT A.USER_NM,
A.AVID
FROM RCX1.VRA_CNFG AS A,
RCX1.VMSTR_DIR AS B
WHERE A.AVID = B.AVID
AND A.AVID NOT IN (SELECT AVID FROM RCX1.VMSTR_DIR);
However, the result set produced no output. I don’t know if that’s because there is no output to produce, or if the logic is wrong.
Thanks for the help ...
Kent