Search code examples
sqlsql-serverjoinanti-join

find only parent records with children of certain status across multiple tables

I need all tickets where all the related children records are of status closed. So if one of the children is something else, I don't want the ticket in the result set.

I tried this using the antijoin patern, but my problem is that the children live in different tables.

please see this http://sqlfiddle.com/#!3/febde/8 for a example

t1: ticket t2: relatedrecord t3: child1 t4: child1

select ticket.ticketid, rr.relatedrecordkey, rr.relatedrecordclass, c1.id, c1.status, c2.id, c2.status
from ticket
inner join relatedrecord rr on rr.recordkey = ticket.ticketid and rr.class = ticket.class
left join child1 c1 on c1.id = rr.relatedreckey and c1.class = rr.relatedrecclass
left join child2 c2 on c2.id = rr.relatedreckey and c2.class = rr.relatedrecclass

results in:

+----------+--------+-----------+-------+-----------------+---------------+--------+--------+--------+--------+
| ticketid | status | RECORDKEY | class | RELATEDRECCLASS | relatedreckey |   id   | status |   id   | status |
+----------+--------+-----------+-------+-----------------+---------------+--------+--------+--------+--------+
|     1183 | NEW    |      1183 | SR    | WORKORDER       |          1238 | 1238   | NEW    | (null) | (null) |
|     1183 | NEW    |      1183 | SR    | SR              |          1184 | (null) | (null) | 1184   | NEW    |
|     1185 | NEW    |      1185 | SR    | WORKORDER       |          1239 | 1239   | CLOSE  | (null) | (null) |
|     1185 | NEW    |      1185 | SR    | SR              |          1186 | (null) | (null) | 1186   | CLOSE  |
|     1187 | NEW    |      1187 | SR    | WORKORDER       |          1240 | 1240   | CLOSE  | (null) | (null) |
|     1187 | NEW    |      1187 | SR    | SR              |          1188 | (null) | (null) | 1188   | NEW    |
|     1190 | NEW    |      1190 | SR    | SR              |          1191 | (null) | (null) | 1191   | CLOSE  |
|     1192 | NEW    |      1192 | SR    | WORKORDER       |          1241 | 1241   | CLOSE  | (null) | (null) |
+----------+--------+-----------+-------+-----------------+---------------+--------+--------+--------+--------+

so

  • ticket 1183 has two related records, both not closed yet. (reject)
  • ticket 1185 has two related records, both are closed (accept)
  • ticket 1187 has two related records, one is new, one is closed. (reject)
  • ticket 1190 has one related record which is closed (accept)
  • ticket 1192 has one related record (different table) which is closed (accept)
  • ticket 1189 has no related records (reject)

From this set, I only want to see ticket 1185, 1190, 1192 in the resultset. it should look like:

+----------+--------+-----------+-------+-----------------+---------------+--------+--------+--------+--------+
| ticketid | status | RECORDKEY | class | RELATEDRECCLASS | relatedreckey |   id   | status |   id   | status |
+----------+--------+-----------+-------+-----------------+---------------+--------+--------+--------+--------+
|     1185 | NEW    |      1185 | SR    | WORKORDER       |          1239 | 1239   | CLOSE  | (null) | (null) |
|     1185 | NEW    |      1185 | SR    | SR              |          1186 | (null) | (null) | 1186   | CLOSE  |
|     1190 | NEW    |      1190 | SR    | SR              |          1191 | (null) | (null) | 1191   | CLOSE  |
|     1192 | NEW    |      1192 | SR    | WORKORDER       |          1241 | 1241   | CLOSE  | (null) | (null) |
+----------+--------+-----------+-------+-----------------+---------------+--------+--------+--------+--------+

I tried something like:

select ticket.ticketid, rr.relatedrecordkey, rr.relatedrecordclass, c1.id, c1.status, c2.id, c2.status
from ticket
inner join relatedrecord rr on rr.recordkey = ticket.ticketid and rr.class = ticket.class
where not exists (
    select 1 from child1 c1
    where c1.id = rr.relatedreckey and c1.class = rr.relatedrecclass
    and c1.status <> 'CLOSE'
) and not exists (
    select 1 from child2 c2
    where c2.id = rr.relatedreckey and c2.class = rr.relatedrecclass
    and c2.status <> 'CLOSE'
)

This results in the two lines for ticket2 AND one from ticket 3 (since it has one child closed) I'm a bit puzzeled how to solve this correctly.

Solution

  • I found a working answer.

    See this sqlfiddle: http://sqlfiddle.com/#!3/febde/24

    SELECT ticket.ticketid,
   count(rr.relatedreckey) AS children,
   count(wo.id) AS wo,
   count(tt.id) AS sr
FROM ticket
INNER JOIN relatedrecord rr ON rr.recordkey = ticket.ticketid
AND rr.class = ticket.class
LEFT JOIN child1 wo ON wo.id = rr.relatedreckey
AND wo.class = rr.relatedrecclass
AND wo.status = 'CLOSE'
LEFT JOIN child2 tt ON tt.id = rr.relatedreckey
AND tt.class = rr.relatedrecclass
AND tt.status = 'CLOSE'
GROUP BY ticket.ticketid 
HAVING (count(wo.id) + count(tt.id)) = count(rr.relatedreckey);

    basically it counts for each related type the number of the closed records and compares it to the total number of relatedrecords. If they are the same, all relatedrecords must be closed.