Charlotte Au Charlotte Au - 4 months ago 5x
SQL Question

views/ queries for multiple foreign keys referencing same primary keys

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;

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)

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)


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