Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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.​
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Loops Section Overview

886 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