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     

Answers


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  

Need Your Help

Android SQLite Example

android database sqlite sqliteopenhelper

I am new to Android and I have to create an application where I need to use a SQLite database.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.