CODEWITHSUNDEEP
sqloracle-databasesql-view
Gideon Sassoon
Gideon Sassoon

Reputation: 604

CREATE VIEW Using multiple tables

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

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

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
);

Upvotes: 5

Views: 51118

Answers (2)

Gideon Sassoon
Gideon Sassoon

Reputation: 604

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

Upvotes: 10

Kerri Williams
Kerri Williams

Reputation: 39

You are getting too many values error for your first view because in your inner query you are returning many columns but in the where clause you have only one column.

Also in your second code please alter like below

 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;

Upvotes: 0

Related Questions