We help IT Professionals succeed at work.

Join does not select all records

91 Views
Last Modified: 2015-08-05
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?
Comment
Watch Question

Doug BishopDatabase Developer

Commented:
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.

Author

Commented:
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

Author

Commented:
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.

Author

Commented:
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.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.