Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Getting a list of id's which have multiple duplicate values that meet a specific condition

I have this query:

SELECT u.id, u.service, u.subscription_date 
FROM user u

WHERE EXISTS (
    SELECT 1
    FROM user u2
    WHERE u2.subscription_date = u.subscription_date
    AND u2.service = u.service
    GROUP BY u2.service, u2.subscription_date
    HAVING COUNT(*) > 1
);

My issue is that I do not just wish to find a user that has duplicate values of subscription_date and service. I want the query to specifically give me a list of user id’s where the same service AND subscription date appears twice.

To clarify:

Say I have the following users:

user_id: 001, 
subscription_date: 2024-01-01
service: basic

user_id: 002, 
subscription_date: 2024-01-01
service: plus

user_id: 002, 
subscription_date: 2023-01-01
service: plus

user_id: 003, 
subscription_date: 2024-01-01
service: premium

user_id: 003, 
subscription_date: 2024-01-01
service: premium

In the query I wish to run, it should only return user id 003, because while user id 002 also has a duplicate of the service, it does not have a duplicate subscription date.

I can’t figure out how to translate this condition into a query.

Tags:

2

Answers


  1. 0

    This sounds like a simple

    select user_id,
       service,
       subscription_date
from users
group by user_id,service,subscription_date
having count(*) > 1;

    Another method would be using COUNT(*) OVER() window function (it requires MySQL 8+)

    Query,

    select user_id,
       subscription_date,
       service
from (  
       select *,
              count(*) over (partition by user_id,subscription_date,service order by user_id asc ) as cnt 
       from users
     ) sub
where cnt > 1;

    Result,

    user_id subscription_date   service
    3      2024-01-01       premium
    3      2024-01-01       premium

    Example

    Login or Signup to reply.
  2. 0

    It seems you need just records having duplicates on all columns – id, subscription_date and service, in this case it is just a simple group by on all columns:

    SELECT * 
FROM user
GROUP BY user_id, service, subscription_date
HAVING COUNT(*) > 1

    or replace * with list of these 3 columns if there are more columns in the table user.

    https://dbfiddle.uk/pWROWL0T

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search