Find child records where status is complete 2

[tennisguy]

Hello...

I have a parent table called Tickets (tech-support tickets) and a related child table named Tasks, related via TicketID. One ticket can be made up of one or more tasks. Each ticket has a status of open or closed. Each task has a status of completed, waiting, etc.

I'm trying to locate all tickets where all of its tasks are marked completed. Once those tickets are found, I can then go into those ticket records and mark them as closed. But, I'm having a challenge trying to query for those parent ticket records whose status is open and where all of the associated child task records are marked as completed.

Any ideas?

Thank you,
Michael

 

[drtree]

Hi,

How about

SELECT TicketID
FROM Tickets
WHERE NOT TicketID IN (SELECT TicketID
                       FROM Tasks
                       WHERE Status <> 'completed')

Essentially, I'm just looking for any Ticket ID that doesn't have an id number listed in the list of tasks that has a status that isn't equal to completed.

HTH,

Doc Tree

 

[bborissov]

SELECT Tickets.*
FROM Tickets
INNER JOIN (SELECT Tbl1.TicketId
                   FROM (SELECT TicketId, COUNT(*) AS Closed
                                FROM Tickets
                         WHERE StatusId = 'Closed'
                         GROUP BY TicketId) Tbl1
            INNER JOIN (SELECT TicketId, COUNT(*) AS All
                               FROM Tickets
                         GROUP BY TicketId) Tbl2
                  ON Tbl1.TicketId = Tbl2.TicketId AND
                     Tbl1.Closed   = Tbl2.All) Tbl3
ON Tickets.TicketId = Tbl3.TicketId

NOT TESTED

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[tennisguy]

Thank you both! Very helpful!