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?
swendellAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dbbishopCommented:
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.
0
swendellAuthor 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
0
swendellAuthor 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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Download your Guide
swendellAuthor 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.
0
Scott PletcherSenior DBACommented:
The first query, rewritten.  When you use an OUTER JOIN to a table, if a matching row is not found, all columns from that table are set to null.  Therefore, a "WHERE ... = ..." against those columns can never be true.  To avoid that, you need to move all the conditions to the JOIN itself.

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
 AND FileActions_1.ActionDefID = 2167
 AND FileActions_1.Live = 1
INNER JOIN dbo.FileActionGroups FileActionGroups_1
  ON FileActions_1.FileActionGroupsID = FileActionGroups_1.FileActionGroupsID
WHERE FileActions.ActionDefID = 66
AND FileActions.Live = 1

ORDER BY FileMain.FileID
1
PortletPaulfreelancerCommented:
When you include a OUTER joined table into the WHERE clause you run the risk of destroying that OUTER join. Take this silly example, 3 small tables Members, Settings, Preferences. Not all members have settings. 
CREATE TABLE Members	    ([ID] int, [Name] varchar(5)) ;
INSERT INTO Members	
    ([ID], [Name])
VALUES
    (1, 'Peter'),
    (2, 'Paul'),
    (3, 'Mary') ;

CREATE TABLE Settings	    ([Memberid] int, [PreferenceID] int) ;
INSERT INTO Settings	
    ([Memberid], [PreferenceID])
VALUES
    (1, 1),
    (3, 1) ;

CREATE TABLE Preferences	    ([ID] int, [Value] varchar(10)) ;
INSERT INTO Preferences	
    ([ID], [Value])
VALUES
    (1, 'YYYY-MM-DD') ;

Open in new window

If we LEFT OUTER JOIN settings and preferences, plus ask for a preference value of YYYY-MM-DD do we get all members? 
select *
FROM members 
LEFT OUTER JOIN settings
      ON members.id = settings.memberid
LEFT OUTER JOIN preferences
      ON settings.preferenceid = preferences.id
WHERE preferences.value = 'YYYY-MM-DD'
;
| ID |  Name | Memberid | PreferenceID | ID |      Value |
|----|-------|----------|--------------|----|------------|
|  1 | Peter |        1 |            1 |  1 | YYYY-MM-DD |
|  3 |  Mary |        3 |            1 |  1 | YYYY-MM-DD |

Open in new window

If however we don't use the where clause what happens: 
select *
FROM members 
LEFT OUTER JOIN settings
      ON members.id = settings.memberid
LEFT OUTER JOIN preferences
      ON settings.preferenceid = preferences.id
      AND preferences.value = 'YYYY-MM-DD'
;
| ID |  Name | Memberid | PreferenceID |     ID |      Value |
|----|-------|----------|--------------|--------|------------|
|  1 | Peter |        1 |            1 |      1 | YYYY-MM-DD |
|  2 |  Paul |   (null) |       (null) | (null) |     (null) |
|  3 |  Mary |        3 |            1 |      1 | YYYY-MM-DD |

Open in new window

or, if we are more cautious about how we use the where clause: 
select *
FROM members 
LEFT OUTER JOIN settings
      ON members.id = settings.memberid
LEFT OUTER JOIN preferences
      ON settings.preferenceid = preferences.id
WHERE (
        preferences.value = 'YYYY-MM-DD'
     OR preferences.value IS NULL
      )
;
| ID |  Name | Memberid | PreferenceID |     ID |      Value |
|----|-------|----------|--------------|--------|------------|
|  1 | Peter |        1 |            1 |      1 | YYYY-MM-DD |
|  2 |  Paul |   (null) |       (null) | (null) |     (null) |
|  3 |  Mary |        3 |            1 |      1 | YYYY-MM-DD |

Open in new window

So, you need to take care how you use the where clause if using any outer join (left or right) and, to preserve the outer join make sure NULLs can be returned.

Alternatively, there is no point using an outer join and then disallowing NULLs in the result, you might as well use an INNER JOIN
0
swendellAuthor 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
0
swendellAuthor Commented:
Paul,
I see what you are saying about including Nulls in the where clause

Scott I further refined my revision of yours by working with the where clause , switching to left join and basically got the same thing as Jeff Blackwell 

SELECT
  FileMain.FileNumber,
  FileMain.FileID,
  FileActions.ActionDefID,
  FileActions.SentDate,
  FileActions_1.ActionDefID,
  FileActions_1.SentDate
FROM dbo.FileMain
LEFT OUTER JOIN dbo.FileActions
  ON FileActions.FileID = FileMain.FileID
  AND FileActions.ActionDefID = 66
  AND FileActions.Live = 1
LEFT OUTER JOIN dbo.FileActions FileActions_1
  ON FileMain.FileID = FileActions_1.FileID
  AND FileActions_1.ActionDefID = 2167
  AND FileActions_1.Live = 1
WHERE NOT (FileActions_1.ActionDefID IS NULL
AND FileActions.ActionDefID IS NULL)
AND (FileActions.ActionDefID = 66
OR FileActions_1.ActionDefID = 2167)

Open in new window


Both Jeff's and my modification of my modification (of Scott's) are solutions. Scott was first on EE to suggest moving the conditions into the join itself but did not do it for both joins in the example he provided and his final where clause did not address NULLs. Paul did not provide and example but did point out the importance of the NULLs in the final WHERE clause. Jeff got it right out of the gate but did not post here. So I will assign points between Scott and Paul
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
swendellAuthor 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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.