How to get list of users using only one value in multiple rows - MySQL

Question

I have a payment form. It has a column named user_id and payment_type. For each payment, users can have multiple payment types. I want to find users who have only used one payment_type in their lifetime.

Let me explain clearly with an example: Let's say I have the following data:

user_id payment_type
1   UPI
1   NB
2   UPI
2   UPI

For the above, I only want user_id 2 as output because for these two payments, it only uses 1 payment_type.

Can anyone help?

Answer 1

A simple HAVING and COUNT should do the trick:

select user_id
from my_table
group by user_id
having count(distinct payment_type)=1;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=65f673a7df3ac0ee18c13105a2ec17ad

If you want to include payment_type in the result set, use:

select my.user_id,my.payment_type
from my_table my 
inner join ( select user_id
             from my_table
             group by user_id
             having count(distinct payment_type)=1
            ) as t1 on t1.user_id=my.user_id
group by  my.user_id,my.payment_type  ;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=cc4704c9e51d01e4e8fc087702edbe6e