Query showing records that do NOT match in between tables
I have two tables. One is a table of the reports that have been submitted to us. Another is a temp table with the records of reports that should eventually be submitted to us. I would like to show only the records in the temp table that do not match the ones in the reports table (so the reports that still must be submitted are displayed).
Example data is:
Reports table: CREATE TABLE [dbo].[Reports] ( [ReportID] [int] IDENTITY(1,1) NOT NULL, [ReportDate] [date] NULL, [AssessmentID] [int] NOT NULL, [ReportType] [varchar](50) NULL ); AssessmentID ReportType ReportID 1 1st Quarterly 27 2 1st Quarterly 30 2 2nd Quarterly 31 2 3rd Quarterly 32 QuarterlyReportsDue table: CREATE TABLE #QuarterlyReportsDue ( AssessmentID INT, InstallationDate DATE, QuarterlyReportType VARCHAR(50) ); AssessmentID InstallationDate QuarterlyReportType 1 2009-08-14 1st Quarterly 1 2009-08-14 2nd Quarterly 1 2009-08-14 3rd Quarterly 1 2009-08-14 4th Quarterly 2 2008-05-16 4th Quarterly 2 2008-05-16 3rd Quarterly 2 2008-05-16 2nd Quarterly 2 2008-05-16 1st Quarterly
I have tried LEFT OUTER JOINS but am running into issues. Please see my below SQL:
SELECT #QuarterlyReportsDue.InstallationDate, #QuarterlyReportsDue.QuarterlyReportType, Reports.ReportType FROM #QuarterlyReportsDue LEFT OUTER JOIN Reports ON #QuarterlyReportsDue.AssessmentID = Reports.AssessmentID WHERE Reports.ReportType IN ('1st Quarterly', '2nd Quarterly', '3rd Quarterly', '4th Quarterly') AND Reports.ReportType <> #QuarterlyReportsDue.QuarterlyReportType ORDER BY #QuarterlyReportsDue.AssessmentID
And my results:
AssessmentID QuarterlyReportType ReportType ReportID 1 2nd Quarterly 1st Quarterly 27 1 3rd Quarterly 1st Quarterly 27 1 4th Quarterly 1st Quarterly 27 2 4th Quarterly 1st Quarterly 30 2 4th Quarterly 2nd Quarterly 31 2 4th Quarterly 3rd Quarterly 32 2 1st Quarterly 2nd Quarterly 31 2 1st Quarterly 3rd Quarterly 32 2 3rd Quarterly 1st Quarterly 30 2 3rd Quarterly 2nd Quarterly 31 2 2nd Quarterly 1st Quarterly 30 2 2nd Quarterly 3rd Quarterly 32
For assessment 1 it works great, assessment 2 has many duplicates. How can I get around this to only show the ideal results?
AssessmentID QuarterlyReportType ReportType 1 2nd Quarterly 1st Quarterly 1 3rd Quarterly 1st Quarterly 1 4th Quarterly 1st Quarterly 2 4th Quarterly
When you LEFT JOIN to a table and then reference one of that table's columns in the WHERE clause, you implicitly turn the join into an INNER JOIN. Instead, move those conditions out of the WHERE and make them part of the JOIN conditions.
SELECT q.InstallationDate, q.QuarterlyReportType, Reports.ReportType FROM #QuarterlyReportsDue q LEFT OUTER JOIN Reports r ON q.AssessmentID = r.AssessmentID AND q.QuarterlyReportType = r.ReportType AND r.ReportType IN ('1st Quarterly', '2nd Quarterly', '3rd Quarterly', '4th Quarterly') WHERE r.AssessmentID IS NULL /* matching record not found in Reports table */ ORDER BY #QuarterlyReportsDue.AssessmentID