Solved

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

Posted on 2011-02-27
3
651 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 250 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 250 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Updating a table from a temp table 4 36
How can i make query faster by writing T-SQL? 6 35
export sql results to csv 6 34
Email Header Detail 12 53
Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

786 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