Reputation: 1
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
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