SQL Query; Exclude record if subrecord = criteria

Question

I am using Report Builder to generate a report on incident records. Each incident record can have one or more Assignment records associated with it. Assignments are stored in a different table (Task) and joined on the parent record ID (Incident table).

As currently configured I get a report that shows each incident ID that meets my criteria and all associated assignments.

I want to exclude any records where any of the assignments are "Property Systems". If I put in a statement in to exclude those records, I get all the records that meet my other criteria, but the assignment to "Property Systems" are excluded.

Example: If record 4 has 3 assignments associated with it. If any of those assignments (Task Owner) = "Property Systems" I want to exclude the entire record.

I want to know how to exclude the entire record, not just the assignment which is what I'm getting now.

 SELECT
  Incident.IncidentNumber
 ,Incident.Status
 ,Incident.CreatedDateTime
 ,Incident.OwnerTeam [Incident Owner]
 ,Incident.Service
 ,Task.AssignedDateTime
 ,Task.OwnerTeam [Task Owner]
 ,Task.ParentRecordNumber
 ,Task.AssigneeFullName

FROM

Incident
  CROSS JOIN Task

WHERE

  Incident.IncidentNumber = Task.ParentRecordNumber
  AND Incident.Status IN (N'Active', N'Waiting')
  AND Incident.Service LIKE N'Property - Two-Way'
  AND Incident.OwnerTeam LIKE N'Corporate & Property Service Desk'
  ---- AND Task.OwnerTeam <> N'Property Systems'

And what is the term for the multiple records associated with the parent record? Subrecords?

Answer 1

You need to check the Task table again.

SELECT
  I.IncidentNumber
 ,I.Status
 ,I.CreatedDateTime
 ,I.OwnerTeam [Incident Owner]
 ,I.Service
 ,T.AssignedDateTime
 ,T.OwnerTeam [Task Owner]
 ,T.ParentRecordNumber
 ,T.AssigneeFullName
FROM
  Incident I
  JOIN 
  Task T ON I.IncidentNumber = T.ParentRecordNumber
WHERE
  Incident.Status IN (N'Active', N'Waiting')
  AND Incident.Service LIKE N'Property - Two-Way'
  AND Incident.OwnerTeam LIKE N'Corporate & Property Service Desk'
  AND NOT EXISTS (SELECT * 
          FROM Task T2 
          WHERE 
             T2.OwnerTeam = N'Property Systems' AND 
             T2.ParentRecordNumber = I.IncidentNumber)

Note the correct JOIN syntax too