| Nested Queries with Select Statement [message #684321] |
Mon, 10 May 2021 06:01  |
 |
a_naq
Messages: 13
Registered: April 2021
|
Junior Member |
|
|
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
User Table
Id | userRegionId
EID-999 | 12345
EID- 888 | 12345
D-900 | 2322
F-943 | 6767
UserId | RestrictionGroup
EID-999| A1
EID-888 | B1
EID-999 | C1
F-943 | Z1
F-943 | X1
UserRegionId | Count of Users having restriction Group >1
12345 | 1
6767 | 1
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
|
|
|
|
| Re: Nested Queries with Select Statement [message #684322 is a reply to message #684321] |
Mon, 10 May 2021 06:34  |
John Watson
Messages: 8804
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Is this a college homework assignment? If so, I think you may need to adjust your relational model, the normalization doesn't look right to me.
As I see it, you have three entities: users, groups, and regions. You need a table for each of these. The relationship between region and user is one-to-many (simple), but the relationship between user and group is many-to-many. You would resolve the many-to-many relationship by putting an intersection table (which some people refer to as a "junction table" or an "associative entity") in the middle. Then your query may be much simpler to write.
Does that make any sense?
|
|
|
|
|
|
|
|
| Re: Nested Queries with Select Statement [message #684326 is a reply to message #684325] |
Mon, 10 May 2021 11:32 |
|
a_naq
Messages: 13
Registered: April 2021
|
Junior Member |
|
|
All the test cases are -
User Table
CREATE TABLE USR (ID VARCHAR(50), userregionid VARCHAR (50));
INSERT INTO USR values ('EID-999', 12345);
INSERT INTO USR values ('EID- 888', 12345);
INSERT INTO USR values ('D-900', 2322);
INSERT INTO USR values ('F-943',6767);
INSERT INTO USR values ('F-900',6767);
INSERT INTO USR values ('F-800',6767);
Select * from USR
ID USERREGIONID
EID-999 12345
EID- 888 12345
D-900 2322
F-943 6767
F-900 6767
F-800 6767
CREATE TABLE userRestriction (userId VARCHAR2(50), restrictionGroup VARCHAR2 (50));
INSERT INTO userRestriction values ('EID-999', 'A1');
INSERT INTO userRestriction values ('EID-888' , 'B1');
INSERT INTO userRestriction values ('EID-999' , 'C1');
INSERT INTO userRestriction values ('F-943' , 'Z1');
INSERT INTO userRestriction values ('F-943' , 'X1');
INSERT INTO userRestriction values ('F-900' , 'X1');
INSERT INTO userRestriction values ('F-900' , 'G1');
INSERT INTO userRestriction values ('F-900' , 'H1');
INSERT INTO userRestriction values ('F-800' , 'G1');
INSERT INTO userRestriction values ('F-800' , 'X1');
select * from userRestriction;
USERID RESTRICTIONGROUP
EID-999 A1
EID-888 B1
EID-999 C1
F-943 Z1
F-943 X1
F-900 X1
F-900 G1
F-900 H1
F-800 G1
F-800 X1
USERREGIONID COUNT(UR.USERID)
12345 1
6767 3
Please let me know if the problem statement is clear or not. Thank you very much for your guidance.
|
|
|
|
|
|
|
|
|
|
| Re: Nested Queries with Select Statement [message #684399 is a reply to message #684327] |
Tue, 25 May 2021 06:00 |
|
a_naq
Messages: 13
Registered: April 2021
|
Junior Member |
|
|
In this query I had to add another condition. The condition was that I had to include all the users who have done purchases in the year 2021 also.
My, Table
CREATE TABLE purchases (employee VARCHAR2(50), purchaseDate Date);
insert into registration values('E-999','31-DEC-2019')
insert into registration values('Y-8222','4-DEC-2021')
insert into registration values('Y-8222','4-DEC-2019')
insert into registration values('E-999','31-DEC-2021')
insert into registration values('E-888','1-JAN-2019')
with t as (
select e.userregionid,
e.id,
r.item,
ur.restrictiongroup,
r.interval,
count(distinct r.interval)
over(partition by e.id,r.item) collission_indicator
from employee e,
userrestriction ur,
restrictions r
where
e.userregionid = '12345'
and ur.userid = e.id
and ur.restrictiongroup = r.restrictiongroup and e.id in(select employee
from purchases where purchaseDate>= '1-JAN-2021')
)
select userregionid, count(distinct id) as No_of_Users
from t
where collission_indicator > 1
group by userregionid
Is there any other efficient way in which I can re-write this query with this condition ?
Thanks for your guidance!
|
|
|
|
|
|
|
|
|
|
| Re: Nested Queries with Select Statement [message #684404 is a reply to message #684403] |
Wed, 26 May 2021 07:05 |
|
a_naq
Messages: 13
Registered: April 2021
|
Junior Member |
|
|
This is the execution plan of the sample test data that I have posted above. My actual database has thousands of entries.
PLAN_TABLE_OUTPUT
Plan hash value: 543829509
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 402 | 11 (19)| 00:00:01 |
| 1 | SORT GROUP BY | | 6 | 402 | 11 (19)| 00:00:01 |
|* 2 | HASH JOIN | | 6 | 402 | 10 (10)| 00:00:01 |
| 3 | TABLE ACCESS FULL | USR | 6 | 240 | 3 (0)| 00:00:01 |
| 4 | VIEW | | 6 | 162 | 7 (15)| 00:00:01 |
|* 5 | HASH GROUP BY | | 6 | 378 | 7 (15)| 00:00:01 |
|* 6 | HASH JOIN SEMI | | 6 | 378 | 6 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL| USERRESTRICTION | 10 | 270 | 3 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL| REGISTRATION | 3 | 108 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("R"."USERID"="U"."ID")
5 - filter(COUNT(*)>1)
6 - access("USERID"="EMPLOYEE")
8 - filter("REGISTRATIONDATE">=TO_DATE(' 2020-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
[Updated on: Wed, 26 May 2021 07:07] Report message to a moderator |
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
