[SOLVED] SQL Query; Exclude record if subrecord = criteria

SQL Query; Exclude record if subrecord = criteria

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?

Solution

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