Click here to Skip to main content
14,486,704 members
Rate this:
Please Sign up or sign in to vote.
See more:
I am working with the SSDI released in 2013 and another death record. However, when trying to see how many records match by SSN I am getting zero results returned. Here is the code that I am using:

select allcalSSNs.Last_name,allcalSSNs.First_name,allcalSSNs.ssn from ssdm JOIN allcalSSNs ON allcalSSNs.SSN = ssdm.ssn 


I have even tried a sub select when also returned zero results.

I have never ran into this issue before with any table I have tried this on.

there are approximately 9,000,000 records in the allcalSSNs and there are about 91,000,000 in the SSDI.

I could really use some guidance on this since I know there are some examples that should be in both.

What I have tried:

Already stated in above sentence: both sub select and inner join/join method.
Posted
Updated 15-Mar-20 21:14pm

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

If I set up trivial data tables to match your query:
CREATE TABLE [dbo].[allcalSSNs](
	[ssn] [int] NOT NULL,
	[Last_name] [nchar](10) NOT NULL,
	[First_Name] [nchar](10) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[ssdm](
	[ssn] [int] NOT NULL
) ON [PRIMARY]

INSERT [dbo].[allcalSSNs] ([ssn], [Last_name], [First_Name]) VALUES (2, N'2222      ', N'222222    ')
INSERT [dbo].[allcalSSNs] ([ssn], [Last_name], [First_Name]) VALUES (4, N'4444      ', N'444444    ')
INSERT [dbo].[ssdm] ([ssn]) VALUES (1)
INSERT [dbo].[ssdm] ([ssn]) VALUES (2)
INSERT [dbo].[ssdm] ([ssn]) VALUES (3)
And I run your query:
select allcalSSNs.Last_name,allcalSSNs.First_name,allcalSSNs.ssn from ssdm JOIN allcalSSNs ON allcalSSNs.SSN = ssdm.ssn
I get What I expect: one row of data:
Last_name   First_name  ssn
2222        222222      2
So if your query isnl;t returning dtaa when run against your live DB then one of three things is happening:
1) That's not the query you are running.
2) You are running it against the wrong DB
3) The data in your DB is not what you expected.

We can't help you with that: we have no access to your DB at all.
   
Comments
Member 11856456 16-Mar-20 16:25pm
   
Hey Griff, if I individually look for the social in each table I can find it. Is it possible that having similar but not the same table structure may be influencing these results.

allcalssns:
Last_name First_name Middle_name Month_of_death Day_of_death Year_of_death County_of_death sex ssn Month_of_birth Day_of_birth Year_of_birth Place_of_birth Race Death_certificate_number Cause_of_death Suffix Place_of_death State_of_death


SSdm:
SSN Last name Suffix First name Middle name Verify or Proof code Death month Death day Death year Birth month Birth day Birth year State or Country code of Residence Zip code of last residence Zip code lump sum payment State_of_death State_of_birth County_of_death Sex

Also when i cut and paste into excel it would seem that some of my column values are thrown to the second line. Not sure if that means anything or if excel just does that.
Richard Deeming 17-Mar-20 8:05am
   
It sounds like you've got unprintable characters in some of your columns. For the join to work, the column values need to be exactly the same. (Trailing white-space is usually ignored, but carriage-return and line-feed characters aren't.)
Member 11856456 18-Mar-20 19:40pm
   
Thank you Richard, I retrieved code to remove hidden special characters and to remove line breaks, and guess what? It worked!!!! Thank you for the idea, I thought something else might be going on.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100