CREATE VIEW Using multiple tables

Question

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

Answer 1

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