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
276 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
2 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Sum causes too much time 5 41
SQL Query 26 62
SQL Server 2012 rs - Field1 and Field2 add 1 to Total 4 18
make null the repeated levels 2 18
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

774 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