title: CREATE VIEW Using multiple tables
body: <p>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</p>
  
  <p>Of course this include two WHERE statements which I am unsure how to implement
  The first is where STAFFJOBNAME "MANAGER"</p>
  
  <p>and the second is where the ADDRESSNO matches on both tables</p>
  
  <pre><code>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';
  </code></pre>
  
  <p>I also have this version. So I'm not sure which is more closer to the correct one.</p>
  
  <pre><code>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
  );
  </code></pre>