Geek Answers Handbook

A query that displays records that do NOT match between tables

I have two tables. One of them is a table of reports that were presented to us. Another is a temporary table with records of reports that should ultimately be presented to us. I would like to show only entries in the temp table that do not match those in the report table (therefore, reports that still need to be sent are displayed).

Sample data:

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 tried LEFT OUTER JOINS but I have problems. See mine 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 are:

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 grade 1 it works great, grade 2 has a lot of duplicates. How can I get around this to show only perfect results?

AssessmentID    QuarterlyReportType ReportType  
1   2nd Quarterly   1st Quarterly  
1   3rd Quarterly   1st Quarterly  
1   4th Quarterly   1st Quarterly  
2       4th Quarterly
+5
source share
5 answers

LEFT JOIN , WHERE, INNER JOIN. WHERE JOIN.

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
+5

NOT EXISTS temp, .

+3

(), .

, AssessmentId, AssessmentId. , , ReportType, .

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

Is something like this possible?

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
+2
source 
select *
from Reports r
left join #QuarterlyReportsDue d 
    on d.AssessmentID=r.AssessmentID
    and d.QuarterlyReportType=ReportType
where d.AssessmentID is null
0
source

More articles:


All Articles