Missing records on my query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Missing records on my query

Author

Topic

gschwab
Starting Member

7 Posts

Posted - 2012-09-19 : 17:54:05

Hi,

I'm new to the forum and fairly new to SQL in general. I'm trying to write what I thought was a very simple query, but I'm not returning all of the records that I would expect to see. Basically I want to return a list of new claims entered in a specific time period and I want to make sure that information in 3 tables has been entered. The problem is that I know there are 9 claims, but I'm only returning 2 claims because there are no records on one of the tables, meaning that the task has not been completed. I hope that makes sense.

Here is my script:

SELECT
c.ClaimNumber,
a.LastFirstName,
c.LossDate,
cla.AdministratorReportedDate,
ee.EntryDate,
ee.EnteredBy,
er.EntryDate,
er.EnteredBy,
dr.EntryDate,
dr.EnteredBy

FROM
dbo.Claimant cla (NOLOCK)
INNER JOIN dbo.Claim c (NOLOCK)
ON cla.ClaimID = c.ClaimID
INNER JOIN dbo.ContactEmployee ee (NOLOCK)
ON cla.ClaimantID = ee.ClaimantID
INNER JOIN dbo.ContactEmployer er (NOLOCK)
ON cla.ClaimantID = er.ClaimantID
INNER JOIN dbo.ContactPhysician dr (NOLOCK)
ON cla.ClaimantID = dr.ClaimantID
INNER JOIN dbo.AddressBook a (NOLOCK)
ON cla.AddressBookID = a.AddressBookID
INNER JOIN dbo.Policy pol (NOLOCK)
ON c.PolicyID = pol.PolicyID

WHERE c.policyID='18' AND c.EntryDate >= '2012-09-01'

Because there is no record in the ContactPhysicians table, then none of the records are being returned by my query. Only the claims that have all of the tables completed. Any ideas on how I can show all of the records that match my c.EntryDate criteria?

Thanks in advance for any help!

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-19 : 17:58:44

Use an Outer Join rather than an Inner Join.

-Chad