?
Solved

Find records in one table that doesnt exist in another - Crystal Reports 11

Posted on 2011-02-27
3
Medium Priority
?
659 Views
Last Modified: 2012-06-27
Hello experts,

I have two tables that have similar demographic info, but are without any primary keys or identifiers.  I have a need to find all of the records in the person table, that do not exist on the subscriber table.  They have similar columns, but are named a little different - same datatypes though.  Here is my script, and I'm having trouble with the syntax, as I am not sure which column to join on.  I need to make sure that lastname, firstname, and date_of_birth are the three pieces of data that dont match in the other table:

select n.subscriber_firstname, n.subscriber_lastname, n.subscriber_date_of_birth, n.SUBSCRIBER_SSN
from NGC_SUBSCRIBER n
left join person p
on n.SUBSCRIBER_SSN = p.ssn
where n.SUBSCRIBER_LASTNAME <> p.last_name
and n.SUBSCRIBER_FIRSTNAME <> p.first_name
and n.SUBSCRIBER_DATE_OF_BIRTH <> p.date_of_birth
and p.last_name is null

Thoughts?

Thanks!
0
Comment
Question by:robthomas09
3 Comments
 
LVL 9

Accepted Solution

by:
rg20 earned 1000 total points
ID: 34993064
You say you want the data from the persons table that doen't exist in the subscriber table
but your query

select n.subscriber_firstname, n.subscriber_lastname, n.subscriber_date_of_birth, n.SUBSCRIBER_SSN
from NGC_SUBSCRIBER n
left join person p
on n.SUBSCRIBER_SSN = p.ssn
where n.SUBSCRIBER_LASTNAME <> p.last_name
and n.SUBSCRIBER_FIRSTNAME <> p.first_name
and n.SUBSCRIBER_DATE_OF_BIRTH <> p.date_of_birth
and p.last_name is null


is returning the subscriber data, should it not be the p.last_name, p.first_name, p.date_of_birth?
0
 
LVL 10

Assisted Solution

by:Jaax
Jaax earned 1000 total points
ID: 34993080
select p.first_name
, p.last_name
, p.date_of_birth
, p.ssn
from person p
where p.ssn not in (select n.SUBSCRIBER_SSN from NGC_SUBSCRIBER n)
0
 

Author Comment

by:robthomas09
ID: 34993088
Thanks for the replies -rg20 - I'm looking for the values in the subscriber table that arent in the person table. I posted that incorrectly up top sorry about that.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
This video tutorial shows you the steps to go through to set up what I believe to be the best email app on the android platform to read Exchange mail.  Get the app on your phone: The first step is to make sure you have the Samsung Email app on your …
Suggested Courses

621 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