查询问题:右外连接. [英] Query Problem : Right Outer Join.

问题描述

你好，
我的架构中有三个表，

Hello,
I have Three Tables in My Schema,

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
  )

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
  )

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
  )

我正在使用C#.net表单向用户授予特权，以便他们访问表单及其功能.

现在，我需要设计一个表单.管理员将能够列出所有公司，然后他将选择要授予访问权限的用户名，为他选择特定特权并保存！

我已经编写了右外部联接查询，但是没有得到预期的结果，
能给我建议吗?

查询:

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 :

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)

推荐答案

为什么不有几个列表...一个列出了所选用户的当前表单/权限，另一个列出了可用的表单.

有两个查询，一个带有内键(用于获取已分配的表单)，一个带有简单的左联接，以获取未分配的表单...编写一些代码以对单击某些内容做出反应(一个按钮)将表单从一个列表移动到另一个列表...

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...

这篇关于查询问题:右外连接.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！