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] [varcha开发者_开发技巧r](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
You should use NOT EXISTS
to find entries in the temp table that do not have matching entries in the submitted reports table.
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
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
)
select *
from Reports r
left join #QuarterlyReportsDue d
on d.AssessmentID=r.AssessmentID
and d.QuarterlyReportType=ReportType
where d.AssessmentID is null
精彩评论