Return multiple rows where count in the Database is more than 1 matching on 2 columns. If BOTH columns match in another row, return both rows

Question

I am querying our IT service management system and want to bring back tickets that match the following criteria.

1. Must be an incident
2. Must be in an open state
3. The user who logged it must have another ticket open with the same problem type (probcode)

It's 3 I'm having issues with. I can easily identify users with more than 1 open ticket but can't work out how to filter on that AND the probcode at the same time.

I tried identifying tickets that meet the criteria using the below SQL but it still brought back tickets where the user had more than one open ticket irrespective of whether or not their other ticket has the same probcode or not.

(
SELECT Cust_ID
FROM opencall
WHERE callclass="Incident"
  AND Status NOT IN (6,16,17,18)
GROUP BY Cust_id, probcode
HAVING (COUNT(cust_id) > 1
        AND COUNT(probcode) > 1)
)

Answer 1

You you state that you "want to bring back tickets that match", so to get all the incident detail you probably should avoid grouping. Hence I believe this is a situation that suits using EXISTS which can return all incidents for users who have more than one open ticket with the same problem code. e.g:

SELECT
       o1.*
FROM opencall o1
WHERE o1.callclass = "Incident"
    AND o1.status NOT IN (6, 16, 17, 18)
    AND EXISTS (
        SELECT 1
        FROM opencall o2
        WHERE o2.Cust_ID = o1.Cust_ID
            AND o2.probcode = o1.probcode
            AND o2.callref <> o1.callref  -- must be more than 1 incident
            AND o2.callclass = "Incident"
            AND o2.status NOT IN (6, 16, 17, 18)
        )

NB this query will not identify rows where status is null.