Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2011-02-27
3
Medium Priority
?
660 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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
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…
From store locators to asset tracking and route optimization, learn how leading companies are using Google Maps APIs throughout the customer journey to increase checkout conversions, boost user engagement, and optimize order fulfillment. Powered …

608 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