Query Problem : Right Outer Join.

Question

0.00/5 (No votes)

See more:

Hello,
I have Three Tables in My Schema,

SQL

CREATE TABLE "DB_USER_PRIVILEGES"
  (
    "USER_ID"      NUMBER(15,0),
    "FORM_ID"      NUMBER(15,0),
    "VIEW_TO"      VARCHAR2(200 CHAR),
    "CREATE_TO"    VARCHAR2(200 CHAR),
    "UPDATE_TO"    VARCHAR2(200 CHAR),
    "DELETE_TO"    VARCHAR2(200 CHAR),
    "PRINT_TO"     VARCHAR2(200 CHAR),
    "RESEND_SMS"   VARCHAR2(200 CHAR),
    "RESEND_EMAIL" VARCHAR2(200 CHAR),
    "STATUS"       VARCHAR2(20 CHAR),
    "EX"           VARCHAR2(200 CHAR),
    "EX1"          VARCHAR2(200 CHAR),
    "EX2"          NUMBER(15,3),
    "EX3"          NUMBER(15,3),
    CONSTRAINT "DB_USER_PRIVILEGES_DB_USE_FK1" FOREIGN KEY ("USER_ID") REFERENCES DB_USERS" ("SRNO") ENABLE,
    CONSTRAINT "DB_USER_PRIVILEGES_FORM_M_FK1" FOREIGN KEY ("FORM_ID") REFERENCES FORM_MASTER" ("FORM_ID") ENABLE
  )

SQL

CREATE TABLE DB_USERS"
  (
    "SRNO"        NUMBER(15,0) NOT NULL ENABLE,
    "DB_USER"     VARCHAR2(20 BYTE),
    "DB_PASSWORD" VARCHAR2(20 BYTE),
    "DB_STATUS"   VARCHAR2(20 BYTE),
    "DB_CREATION_DATE" DATE,
    "USER_NAME"       VARCHAR2(200 CHAR),
    "USER_DEPT_ID"    NUMBER(15,0),
    "USER_COMP_IPADD" VARCHAR2(200 CHAR),
    "EX"              VARCHAR2(200 CHAR),
    "EX1"             VARCHAR2(200 CHAR),
    "EX2"             NUMBER(15,3),
    "EX3"             NUMBER(15,3),
    CONSTRAINT "DB_USERS_PK" PRIMARY KEY ("SRNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" ENABLE,
    CONSTRAINT FOREIGN KEY ("USER_DEPT_ID") REFERENCES DEPARTMENT_MASTER" ("SRNO") ENABLE
  )

SQL

CREATE TABLE FORM_MASTER"
  (
    "FORM_ID"      NUMBER(15,0) NOT NULL ENABLE,
    "FORM_NAME"    VARCHAR2(200 CHAR),
    "FORM_SECTION" VARCHAR2(200 CHAR),
    "STATUS"       VARCHAR2(20 CHAR),
    "EX"           VARCHAR2(200 CHAR),
    "EX1"          VARCHAR2(200 CHAR),
    "EX2"          NUMBER(15,3),
    "EX3"          NUMBER(15,3),
    CONSTRAINT  PRIMARY KEY ("FORM_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" ENABLE
  )

i am Using C#.net Forms to Grant Privileges to User FOr Accessing Form and its Features.

Now I need to Design a Form In WHich Administrator will be able to list of All FOrms, Then He will select User name to Grant Access, Select Specific Privileges for him and Save!

I have written a Right outer Join Query but It does not Give Results as Expected,
Can you Please Suggest me?

Query :

SQL

SELECT FORM_MASTER.FORM_NAME,  FORM_MASTER.FORM_ID,  DB_USER_PRIVILEGES.VIEW_TO,  DB_USER_PRIVILEGES.CREATE_TO,  DB_USER_PRIVILEGES.UPDATE_TO,  DB_USER_PRIVILEGES.DELETE_TO,  DB_USER_PRIVILEGES.PRINT_TO,  DB_USER_PRIVILEGES.RESEND_SMS,  DB_USER_PRIVILEGES.RESEND_EMAIL FROM DB_USERS INNER JOIN DB_USER_PRIVILEGES ON DB_USERS.SRNO = DB_USER_PRIVILEGES.USER_ID RIGHT JOIN FORM_MASTER ON FORM_MASTER.FORM_ID = DB_USER_PRIVILEGES.FORM_ID where(DB_USER_PRIVILEGES.USER_ID=6) OR (DB_USER_PRIVILEGES.USER_ID   IS NULL)

Posted 10-Aug-11 20:08pm

prathameshpitale

Add a Solution

Comments

walterhevedeich 11-Aug-11 2:17am

Would you mind to give your expected result and why you said that it does not give results as expected.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Damian S_ · Answer 1 · 2011-08-10T20:41:00

Why not have a couple of lists... one that lists the current forms/permissions for the selected user, and one that lists the available forms.

Two queries, one with an inner joing (to get the forms they are already assigned) and one with a simple left join to get the forms that they aren't assigned... Write a bit of code to react to clicking something (a button perhaps) to move a form from one list to the other...