swendell
asked on
Join does not select all records
This returns one record only even if I set to full outer joins
SELECT
FileActionGroups.ActionLis
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.FileActionGrou
LEFT OUTER JOIN dbo.FileActions FileActions_1
ON FileMain.FileID = FileActions_1.FileID
INNER JOIN dbo.FileActionGroups FileActionGroups_1
ON FileActions_1.FileActionGr
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.FileActionGr
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?
SELECT
FileActionGroups.ActionLis
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.FileActionGrou
LEFT OUTER JOIN dbo.FileActions FileActions_1
ON FileMain.FileID = FileActions_1.FileID
INNER JOIN dbo.FileActionGroups FileActionGroups_1
ON FileActions_1.FileActionGr
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.FileActionGr
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?
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
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
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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.