I am new to Oracle. I have a table where there are multiple restriction groups that are assigned to users. Every group of user belong to a userregionID. I have to display a list of userregionID where users are assigned more than 1 restriction group.
My Tables
user - Id, userregionid
userRestriction - userId, restrictionGroup
For example,
User Table
EID-999 | 12345
EID- 888 | 12345
D-900 | 2322
F-943 | 6767
UserRestriction Table
UserId | RestrictionGroup
EID-999| A1
EID-888 | B1
EID-999 | C1
F-943 | Z1
F-943 | X1
So, my output should come like
UserRegionId | Count of Users having restriction Group >1
12345 | 1
6767 | 1
because user EID-999 and F-943 belong to userregionId 12345 and 6767 respectively and they are assigned more than 1 restriction group.
My Effort
I have written a query that displays the list of users having > 1 restrictionGroup within the same userregionID but I am clueless on how to proceed further and convert this query into a nested query that can only fetch the count and userregionID from the entire database.
My query
select distinct ec.userId, e.userregionid,
count(distinct ec.restrictionGroup) over (partition by ec.userId)
from user e, userRestriction ec
where e.userregionid = '12345' and e.Id= ec.userId
You might not need a nested-query here and a INNER JOIN as below can help you.
select u.userregionid, count(ur.userId)
from userRestriction ur, USR u
where ur.userId=u.id
group by ur.userId , u.userregionid
having count(ur.userId) >1;
PS: A DB-Fiddle here can help you to visualize.