CODEWITHSUNDEEP
sqloracle-database
user2582139
user2582139

Reputation:

Retrieve records through inner join

I have a schema as per the below: 

CREATE TABLE rruser (
  id          NUMBER(32,0)  NOT NULL,
  name        VARCHAR2(30)  NOT NULL,
  fullname    VARCHAR2(100) NOT NULL,
  active_flag CHAR(1)       DEFAULT 'N' NOT NULL
  )


CREATE TABLE rruser_group (
  user_id  NUMBER(32,0) NOT NULL,
  group_id NUMBER(32,0) NOT NULL
)

CREATE TABLE rrgroup (
  id   NUMBER(32,0)  NOT NULL,
  name VARCHAR2(100) NOT NULL,
  code VARCHAR2(20)  NOT NULL
)


CREATE TABLE rrgroup_permission (
  group_id      NUMBER(32,0) NOT NULL,
  permission_id NUMBER(32,0) NOT NULL
)


CREATE TABLE rrpermission (
  id          NUMBER(32,0)   NOT NULL,
  name        VARCHAR2(100)  NOT NULL,
  description VARCHAR2(1000) NOT NULL
)

The connectivity is such that RRUSER is linked to RRGROUP via table RRUSER_GROUP and RRGROUP further linked with RRPERMISSION via table RRGROUP_PERMISSION.

I have to find out the users whose active flag value is equal to 'Y' within RRUSER; I'm using the below query 

SELECT * FROM rruser  WHERE  ACTIVE_FLAG = 'Y'

I then have to find out the users which have write permission; in the last table, RRPERMISSION, there is a column NAME, which has the write permissions where the value of this column is 'write'. What query could I use to get this information? I know it should be achieved using an INNER JOIN.

Right now, I have tried a query for particular user to know whether he has write permission or not and have found out that he has write permission like this...

  SELECT count(ID) FROM rruser WHERE ACTIVE_FLAG = 'Y';
  SELECT * FROM rruser WHERE ACTIVE_FLAG = 'Y' AND FULLNAME = 'sss'
  SELECT * FROM rruser_group WHERE USER_ID = 1100    
  SELECT * FROM rrgroup WHERE ID = 113;
  SELECT * FROM rrgroup_permission WHERE GROUP_ID = 189 ;
  SELECT * FROM rrpermission WHERE ID = 990

Upvotes: 0

Views: 62

Answers (1)

Dhaval Bharadva
Dhaval Bharadva

Reputation: 3083

Try this: 

SELECT ru.* FROM rruser ru
inner join rruser_group rg ON ru.id = rg.user_id
inner join rrgroup_permission rgp ON rg.group_id = rgp.group_id
inner join rrpermission rp ON rgp.permission_id = rp.id WHERE ru.ACTIVE_FLAG='Y' AND rp.name='write'

Upvotes: 1

Related Questions