Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Comparing Values between Tables

Status
Not open for further replies.

ks01

MIS
Aug 11, 2002
110
US
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:

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
 
i think in your actual query, you've reversed the A and B aliases from your description, because USER_NM exists only in the B table, not the A table

you need to do a LEFT OUTER JOIN from the application table to the employee master table
Code:
select B.USER_NM
     , B.AVID
  from RCX1.VRA_CNFG  as B
left outer
  join RCX1.VMSTR_DIR as A
    on B.AVID = A.AVID
 where A.AVID is null

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)
 
Another way keeping your initial logic:[tt]
SELECT USER_NM,
AVID
FROM RCX1.VRA_CNFG
WHERE AVID NOT IN (SELECT AVID FROM RCX1.VMSTR_DIR);[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top