CODEWITHSUNDEEP
mysql
Fnep Shevoo
Fnep Shevoo

Reputation: 1

how to get the list of users with the same value in my database

i want to get the details of a user with the same value in Referid. in my users data i have userid,firstname,surname,othername,username,mobilephone,phonenumber,address, stateofresidence,country,email,referralemail,referalid,user_stage,creation_date, timestamp

i dont know how to structure my code to get users with the same value in referid.

example.

Userid | referid| name | username|
 1     |201     | Jack | jack02  |
 2     |111     | john | john02  |
 3     |331     | phil | frl02   |
 4     |201     | jess | jagg02  |

it will also listed in a tabular form

SELECT 
    users.userid, 
    users.firstname, 
    users.othername, 
    users.surname, 
    users.email,
    users.mobilephone,
    users.referalid,
    users.active,
    stages.stage_description 
FROM users 
INNER JOIN stages ON users.user_stage=stages.stage_id 
GROUP BY users.referalid 
HAVING COUNT(*) > 0

Upvotes: 0

Views: 382

Answers (1)

GMB
GMB

Reputation: 222722

This seems to be addressable with a correlated subquery:

select u.*
from users u
where (select count(*) from users u1 where u1.referid = u.refid) > 1

This will give you the list of all users whose referid is used by at least another user. It is unclear what is the purpose of table stages, so I left it apart.

You can also join with an aggregate query:

select u.*
from users u
inner join (
    select refid, count(*) from users group by refid having count(*) > 1
) g on g.refid = u.refid

Or use a window function in MySQL 8.0:

select *
from (select u.*, count(*) over(partition by refid) cnt from users) x
where cnt > 1

Upvotes: 1

Related Questions