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?
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.
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.
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
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.
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.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy 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
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
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.