?
Solved

How to query two tables to determine if at least one of the column values does not match

Posted on 2013-10-28
2
Medium Priority
?
288 Views
Last Modified: 2013-10-28
I need to compare two tables to count how many emps have the same Applid, but might not have the same SSN, Gender, DOB, City of Birth or State of Birth. Its possible for them to have the same Applid, same Gender, but not SSN or same Applid, same DOB, but not City of Birth..pretty much any combination of the above.

Exact request: How many Applicant Ids where at least one of the following does not match - SSN, Gender, Date of Birth, City of Birth, State of Birth?

Applid table

Applid
Appl_FName
Appl_LName
SSN
Gender
Date of Birth
City of Birth
State of Birth

Person table
Applid
SSN
Gender
Date of Birth
City of Birth
State of Birth


I know to join the tables on Applid but after I am not sure how to proceed. Thanks in advance for the help
0
Comment
Question by:SQLnewbie77
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 39606320
Try something like this:

create table tab1(col1 char(1), col2 char(1), col3 char(1)); 

drop table tab2 purge;
create table tab2(col1 char(1), col2 char(1), col3 char(1)); 


insert into tab1 values('a','a','a');
insert into tab1 values('b','a','b');
insert into tab1 values('c','c','a');

insert into tab2 values('a','a','a');
insert into tab2 values('b','b','a');
insert into tab2 values('c','a','c');
commit;


select t1.*, t2.*
from tab1 t1, tab2 t2
where t1.col1=t2.col1 and
(
	t1.col2 != t2.col2 or
	t1.col3 != t2.col3
)
/

Open in new window

0
 

Author Closing Comment

by:SQLnewbie77
ID: 39606362
thanks!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question