创建视图使用多个表 [英] CREATE VIEW Using multiple tables

问题描述

您好，尝试在oracle中创建视图.但是，我的代码中出现错误，无法解决. 目前，我正在尝试创建一个显示 经理，他们的名字和姓氏也是他们所分配的诊所 诊所的PK和诊所地址的FK 所有地址都存储在该表中(是的，我知道这不是标准的，但这是我选择的方式) 所以我也想显示他们工作的诊所的地址详细信息

Hi trying to create a view in oracle. However I'm getting errors in my code which I can't work out how to solve. Currently I'm trying to create a view which shows Managers, their first and last name the clinic they are assigned too The PK of the clinic and the FK of the address of the clinic All addresses are stored in that table (yes I know it's not standard but it's how I am choosing to do it) So I also want to show the address details of the clinic which they work at

当然，这包括两个我不确定如何实现的WHERE语句 第一个是STAFFJOBNAME"MANAGER"

Of course this include two WHERE statements which I am unsure how to implement The first is where STAFFJOBNAME "MANAGER"

第二个是两个表中ADDRESSNO匹配的地方

and the second is where the ADDRESSNO matches on both tables

CREATE VIEW MANAGER AS
    SELECT STAFF.staffno,STAFF.staffFirstName,STAFF.staffLastName,CLINIC.clinicNo, CLINIC.addressNo
    FROM STAFF,CLINIC
    WHERE addressNo = 
    (
        SELECT addressNo, addressStreet, addressCity, addressCounty, addressPostcode, addressTelephone,
        FROM ADDRESS,
        INNER JOIN CLINIC,
        ON ADDRESS.addressNo = CLINIC.addressNo
    ) AND STAFF.staffJobName = 'MANAGER';

我也有这个版本.因此，我不确定哪种方法更接近正确的方法.

I also have this version. So I'm not sure which is more closer to the correct one.

CREATE VIEW MANAGER
(
    AS
    SELECT STAFF.staffno,STAFF.staffFirstName,STAFF.staffLastName,CLINIC.clinicNo, CLINIC.addressNo, ADDRESS.addressNo, ADDRESS.addressStreet, ADDRESS.addressCity, ADDRESS.addressCounty, ADDRESS.addressPostcode, ADDRESS.addressTelephone,
    FROM ADDRESS, STAFF,
    INNER JOIN CLINIC,
    ON ADDRESS.addressNo = CLINIC.addressNo
);

推荐答案

CREATE VIEW MANAGERANDCLINIC    AS
    SELECT STAFF.staffno,STAFF.staffFirstName,STAFF.staffLastName,CLINIC.clinicNo, ADDRESS.addressStreet, ADDRESS.addressCity, ADDRESS.addressCounty, ADDRESS.addressPostcode, ADDRESS.addressTelephone
    FROM STAFF,CLINIC, ADDRESS
    WHERE (CLINIC.CLINICMANAGERNO = STAFF.STAFFNO) AND
    (CLINIC.ADDRESSNO = ADDRESS.ADDRESSNO)
    ORDER BY CLINIC.CLINICNO;

最终找到了我自己的问题的答案

Eventually found the answer to my own question

这篇关于创建视图使用多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！