Home » SQL & PL/SQL » SQL & PL/SQL » Nested Queries with Select Statement (Oracle 12 c, 12.1.0.1)
Nested Queries with Select Statement [message #684321] Mon, 10 May 2021 06:01 Go to next message
a_naq
Messages: 10
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
For example,

User Table
Id       | userRegionId
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
Re: Nested Queries with Select Statement [message #684322 is a reply to message #684321] Mon, 10 May 2021 06:34 Go to previous messageGo to next message
John Watson
Messages: 8560
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 #684323 is a reply to message #684322] Mon, 10 May 2021 06:55 Go to previous messageGo to next message
a_naq
Messages: 10
Registered: April 2021
Junior Member
Hi, No this is not a college assignment and I have no control over the database, so I can't change its values or change the relational model.
Re: Nested Queries with Select Statement [message #684325 is a reply to message #684323] Mon, 10 May 2021 10:13 Go to previous messageGo to next message
Michel Cadot
Messages: 67884
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

With any SQL or PL/SQL question, please, post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: Nested Queries with Select Statement [message #684326 is a reply to message #684325] Mon, 10 May 2021 11:32 Go to previous messageGo to next message
a_naq
Messages: 10
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
Table UserRestriction
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

Expected Output:

USERREGIONID	COUNT(UR.USERID)
12345	           1
6767	           3
I have to display a list of userregionID where users are assigned more than 1 restriction group.

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 #684327 is a reply to message #684326] Mon, 10 May 2021 11:56 Go to previous messageGo to next message
Michel Cadot
Messages: 67884
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with
  2    restricted_count as (
  3      select userid, count(*) nb_rr
  4      from userRestriction
  5      group by userid
  6    )
  7  select userregionid, count(*) user_count
  8  from USR u, restricted_count r
  9  where r.userid = u.id
 10    and r.nb_rr > 1
 11  group by userregionid
 12  order by 1
 13  /
USERREGION USER_COUNT
---------- ----------
12345               1
6767                3

2 rows selected.
Re: Nested Queries with Select Statement [message #684345 is a reply to message #684327] Wed, 12 May 2021 11:29 Go to previous messageGo to next message
Michel Cadot
Messages: 67884
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Any feedback?

Re: Nested Queries with Select Statement [message #684346 is a reply to message #684345] Wed, 12 May 2021 11:49 Go to previous messageGo to next message
a_naq
Messages: 10
Registered: April 2021
Junior Member
Thanks a lot. Its works ! Thank you again for your help and guidance.
Re: Nested Queries with Select Statement [message #684399 is a reply to message #684327] Tue, 25 May 2021 06:00 Go to previous messageGo to next message
a_naq
Messages: 10
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);
Values

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')
So, I modified the query like this

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
This seems to be working in my test case data where I have less data but in my actual database where there are thousands of values, this query seems to be running indefinitely.

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 #684400 is a reply to message #684399] Tue, 25 May 2021 08:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
What does the EXECUTION PLAN show?
Re: Nested Queries with Select Statement [message #684402 is a reply to message #684400] Tue, 25 May 2021 09:57 Go to previous messageGo to next message
a_naq
Messages: 10
Registered: April 2021
Junior Member
Hi,
I am sorrt im new to this execution plan.
I just opened it in Oracle sql developer,
And it shows a cost of 111593 for the index field(range scan)
Under which it shows the date filter.

I am sorry i dont known of this is what i was suppoed to look. I am again sorry.

[Updated on: Tue, 25 May 2021 09:58]

Report message to a moderator

Re: Nested Queries with Select Statement [message #684403 is a reply to message #684402] Tue, 25 May 2021 10:39 Go to previous messageGo to next message
Michel Cadot
Messages: 67884
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read http://www.orafaq.com/forum/index.php/mv/msg/206002/433888/#msg_433888 and post the required information.

Re: Nested Queries with Select Statement [message #684404 is a reply to message #684403] Wed, 26 May 2021 07:05 Go to previous messageGo to next message
a_naq
Messages: 10
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

Re: Nested Queries with Select Statement [message #684405 is a reply to message #684404] Wed, 26 May 2021 10:02 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
We need to see the EXECUTION PLAN from the production database where the problem resides.
Previous Topic: Present value calculations in Oracle
Next Topic: How to spead up query that calls the save view multiple times
Goto Forum:
  


Current Time: Thu Jun 17 15:53:08 CDT 2021