views/ queries for multiple foreign keys referencing same primary keys

Question

Hello I have got two tables and the staff_id from the risk table is used to look up the name of the owner and contact person. I was wondering if it is possible to create a view which includes both the owner name and staff name. I tried to create a view as shown below but I could only include either owner name or contact name in the view. (For reference I am using derby). Thanks in advance.

CREATE VIEW public_view AS
SELECT r.risk_id, r.risk_name s.staff_name
FROM risk r, staff s
AND r.owner_id = s.staff_id;


CREATE TABLE STAFF
(
    staff_id varchar(8) NOT NULL,
    staff_name varchar(100),
    staff_email_addr varchar(30),
    staff_position varchar(30),
    staff_sect_elem varchar(60),
    CONSTRAINT pk_staff_id PRIMARY KEY (staff_id)
);

CREATE TABLE RISK
(
    risk_id varchar(6) NOT NULL, 
    risk_name varchar(20) NOT NULL,
    risk_desc varchar(20),
    owner_id varchar(8),
    contact_id varchar(8),
    CONSTRAINT pk_risk_id PRIMARY KEY (risk_id),
    CONSTRAINT fk_owner_id FOREIGN KEY (owner_id) REFERENCES STAFF(staff_id), 
    CONSTRAINT fk_contact_id FOREIGN KEY (contact_id) REFERENCES STAFF(staff_id) 
);

Answer 1

Use table aliases:

select *
from RISK r
LEFT OUTER JOIN STAFF o ON r.owner_id = o.staff_id
LEFT OUTER JOIN STAFF c ON r.contact_id = c.staff_id