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?
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.