CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Tim Sumrall
Tim Sumrall

Reputation: 27

TSQL: Find required missing records

I need a query to find Inspection/Component that did not get a "Rating 6" record. I'll be inserting new records so that all Inspection/Component have a "Rating 6" record along with what ever other ratings they may have.

Consider the following data: 57646, 57652 and 57657 are not correct because they are missing their Rating 6 record. In this data example, these three should be the only Inspection/Component returned by the query. 

InspectionID  ComponentID     RatingTypeID
138           57646           10
138           57647           6
138           57647           2
138           57648           6
138           57649           6
138           57650           6
138           57651           10
138           57651           6
138           57652           10
138           57653           6
138           57654           6
138           57655           6
138           57656           6
138           57657           10

Upvotes: 2

Views: 74

Answers (3)

user3096880
user3096880

Reputation: 1

select * from YOURTABLE where componentId not in (select componentId from YOURTABLE where RatingTypeId=6)

Upvotes: 0

Kell
Kell

Reputation: 3317

SELECT A.InspectionID FROM MyTable AS A
LEFT OUTER JOIN MyTable AS B
ON A.InspectionID = B.InspectionID AND B.RatingTypeID = 6
WHERE B.InspectionID IS NULL

Upvotes: 2

Lamak
Lamak

Reputation: 70638

SELECT *
FROM YourTable A
WHERE NOT EXISTS(SELECT 1 FROM YourTable
                 WHERE InspectionID = A.InspectionID  
                 AND ComponentID = A.ComponentID
                 AND RatingTypeID = 6)

Upvotes: 4

Related Questions