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.