troubleshooting Question

Join does not select all records

Avatar of swendell
swendell asked on
Microsoft SQL ServerSQL
9 Comments3 Solutions95 ViewsLast Modified:
This returns one record only  even if I set to full outer joins
SELECT
  FileActionGroups.ActionListGroupDefID,
  FileMain.FileNumber,
  FileMain.FileID,
  FileActions.ActionDefID,
  FileActions.SentDate,
  FileActions_1.ActionDefID,
  FileActions_1.SentDate
FROM dbo.FileMain
INNER JOIN dbo.FileActions
  ON FileActions.FileID = FileMain.FileID
INNER JOIN dbo.FileActionGroups
  ON FileActions.FileActionGroupsID = FileActionGroups.FileActionGroupsID
LEFT OUTER JOIN dbo.FileActions FileActions_1
  ON FileMain.FileID = FileActions_1.FileID

INNER JOIN dbo.FileActionGroups FileActionGroups_1
  ON FileActions_1.FileActionGroupsID = FileActionGroups_1.FileActionGroupsID
WHERE FileActions.ActionDefID = 66
AND FileActions_1.ActionDefID = 2167
AND FileActions.Live = 1
AND FileActions_1.Live = 1
ORDER BY FileMain.FileID


This returns 5 records
SELECT
  FileMain_1.FileNumber,
  FileMain_1.FileID,
  FileActions_1.ActionDefID
FROM dbo.FileActions FileActions_1
INNER JOIN dbo.FileActionGroups FileActionGroups_1
  ON FileActions_1.FileActionGroupsID = FileActionGroups_1.FileActionGroupsID
RIGHT OUTER JOIN dbo.FileMain FileMain_1
  ON FileMain_1.FileID = FileActions_1.FileID

WHERE FileActions_1.ActionDefID = 2167
AND FileActions_1.Live = 1

Why don't they return the same number of records?
How should the top version be written to return all records?
SOLUTION
Join our community to see this answer!
Unlock 3 Answers and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros