Query showing records that do NOT match in between tables

Question

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     

Answer 1

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  

Answer 2

You should use NOT EXISTS to find entries in the temp table that do not have matching entries in the submitted reports table.

Answer 3

Something like this perhaps?

SELECT     qrd.InstallationDate, 
           qrd.QuarterlyReportType
FROM       #QuarterlyReportsDue qrd 
WHERE      qrd.QuarterlyReportType IN ('1st Quarterly', '2nd Quarterly', '3rd Quarterly', '4th Quarterly')
           AND NOT EXISTS (
                 SELECT   1 
                 FROM     Reports r 
                 WHERE    r.AssessmentID = qrd.AssessmentID 
                          AND r.ReportType = qrd.QuarterlyReportType
              )
ORDER BY   qrd.AssessmentID   

Answer 4

This join will multiply the records from two tables into each other (Cartesian), and that is why you are getting more records coming back.

Remember you are joining on AssessmentId and there are multiple records with the same assessmentId. Although you are filtering out those not having same ReportType but you come across new combinations.

SELECT #QuarterlyReportsDue.InstallationDate, 
       #QuarterlyReportsDue.QuarterlyReportType
FROM #QuarterlyReportsDue 
WHERE NOT EXISTS
      (
      SELECT 1 
      FROM Reports 
      WHERE Reports.ReportType = QuarterlyReportsDue.QuarterlyReportType 
      AND #QuarterlyReportsDue.AssessmentID = Reports.AssessmentID 
      )