Avatar of swendell
swendell
 asked on

Join does not select all records

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?
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
swendell

8/22/2022 - Mon
D B

You have only one INNER JOIN on the second query, but three on the first. I would suspect the first query is restricting records based on the inner joins.

LEFT OUTER JOIN will return ALL rows from the first table (in the FROM clause) and only matching ones from the joined table. RIGHT OUTER JOIN is the opposite, it will return ALL rows in the joined table and only matching ones from the table in the FROM clause.
swendell

ASKER
I totally got rid of the inner joins on first query and still only one record returned
SELECT
  FileMain.FileNumber,
  FileMain.FileID,
  FileActions.ActionDefID,
  FileActions.SentDate,
  FileActions_1.ActionDefID,
  FileActions_1.SentDate
FROM dbo.FileMain
FULL OUTER JOIN dbo.FileActions
  ON FileActions.FileID = FileMain.FileID
FULL OUTER JOIN dbo.FileActions FileActions_1
  ON FileMain.FileID = FileActions_1.FileID
WHERE FileActions.ActionDefID = 66
AND FileActions_1.ActionDefID = 2167
AND FileActions.Live = 1
AND FileActions_1.Live = 1
ORDER BY FileMain.FileID
swendell

ASKER
If I delete " FileActions.ActionDefID = 66 AND" from the above query , I get 30 records with FileActions_1.ActionDefID = 2167 and various values  joined for  FileActions.ActionDefID.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
swendell

ASKER
For example: If FileMain can join to FileActions table when ActionDef = 66 yet FileActions_1 table does not have an ActionDef = 2167 for same fileID; that part of record should be null. For some reason the entire record is excluded.
SOLUTION
Scott Pletcher

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
PortletPaul

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
swendell

ASKER
Scott, your query only return one record which was the only one with both 66 and 2167.
I tried to rewrite it and I was able to get all records with 66 and (66 and 2167) but not just 2167 using this modification of your query like this:

SELECT
  FileMain.FileNumber,
  FileMain.FileID,
  FileActions.ActionDefID,
  FileActions.SentDate,
  FileActions_1.ActionDefID,
  FileActions_1.SentDate
FROM dbo.FileMain
FULL OUTER JOIN dbo.FileActions
  ON FileActions.FileID = FileMain.FileID
  AND FileActions.ActionDefID = 66
  AND FileActions.Live = 1
FULL OUTER JOIN dbo.FileActions FileActions_1
  ON FileMain.FileID = FileActions_1.FileID
  AND FileActions_1.ActionDefID = 2167
  AND FileActions_1.Live = 1
WHERE FileActions.ActionDefID = 66
AND FileActions.Live = 1
ORDER BY FileMain.FileID

The where clause at the end of yours did not seem right as Paul was mentioning further down.

Jeff Blackwell (not in thread) suggested this which seems to work:

SELECT
  fm.FileID,
  fm.FileNumber,
  fa1.ActionDefID,
  fa2.ActionDefID,
  fa2.SentDate AS BrokerSigningOrderDate,
  fa1.SentDate AS TitleOrderDate
FROM dbo.FileMain fm
LEFT OUTER JOIN (SELECT
  FileActions.FileID,
  FileActions.ActionDefID,
  FileActions.SentDate
FROM dbo.FileActions
WHERE FileActions.ActionDefID = 66
AND FileActions.Live = 1) fa1
  ON fm.FileID = fa1.FileID
LEFT OUTER JOIN (SELECT
  FileActions.FileID,
  FileActions.ActionDefID,
  FileActions.SentDate
FROM dbo.FileActions
WHERE FileActions.ActionDefID = 2167
AND FileActions.Live = 1) fa2
  ON fm.FileID = fa2.FileID
WHERE fa1.ActionDefID IS NOT NULL
OR fa2.ActionDefID IS NOT NULL
ORDER BY fm.FileID

Paul I need to digest what you wrote before assigning points
ASKER CERTIFIED SOLUTION
swendell

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
swendell

ASKER
This is the actual solution. The above contributions did not totally work so I wanted to post the final working solution based upon the excellent contributions above
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.