SQL query to find the repetitions of data-CodePudding

I've a sample data

id    date        user_id    customer_id     status
 1   2022-06-23      1            12         no response
 2   2022-06-23      1            12         no response
 3   2022-06-24      1            12         no response
 4   2022-06-23      2            15         no response
 4   2022-06-23      2            15         successful
 5   2022-06-23      3            16         call later

I need to fetch those kind of records where a user_id called the same customer_id on the same day got only no response status more than once but not any other statuses.

The result for the above example would be

id
1
2

CodePudding user response：

You can use aggregation and the conditions in the HAVING clause:

SELECT user_id, customer_id, date
FROM tablename
GROUP BY user_id, customer_id, date
HAVING COUNT(*) > 1 AND SUM(status <> 'no response') = 0

If you want the respective rows of the table use the above query with the operator IN:

SELECT *
FROM tablename
WHERE (user_id, customer_id, date) IN (
  SELECT user_id, customer_id, date
  FROM tablename
  GROUP BY user_id, customer_id, date
  HAVING COUNT(*) > 1 AND SUM(status <> 'no response') = 0
);

See the demo.