I have the follwoing query which works but I'm wondering if it could be more efficient. I need the first and last name of the 4 employees from the phonebook table (pb) who's badges (the employees ID) are stored in the Commitment table
SELECT Originator_ID,
(SELECT pb.lname FROM pb WHERE pb.badge = Commitment.Originator_ID) AS Originator_Last_Name,
(SELECT pb.fname FROM pb WHERE pb.badge = Commitment.Originator_ID) AS Originator_First_Name,
Checker_ID,
(SELECT pb.lname FROM pb WHERE pb.badge = Commitment.Checker_ID) AS Checker_Last_Name,
(SELECT pb.fname FROM pb WHERE pb.badge = Commitment.Checker_ID) AS Checker_First_Name,
Reviewer_ID,
(SELECT pb.lname FROM pb WHERE pb.badge = Commitment.Reviewer_ID) AS Reviewer_Last_Name,
(SELECT pb.fname FROM pb WHERE pb.badge = Commitment.Reviewer_ID) AS Reviewer_First_Name,
Approver_ID,
(SELECT pb.lname FROM pb WHERE pb.badge = Commitment.Approver_ID) AS Approver_Last_Name,
(SELECT pb.fname FROM pb WHERE pb.badge = Commitment.Approver_ID) AS Approver_First_Name
FROM Commitment
WHERE Commitment.Approver_ID IN (SELECT pb.badge FROM pb WHERE pb.dept = ?) ORDER BY Commitment_ID
Does my query have too many subqueries?
Or should I normalized and break out the 4 employee badges into a separate table? If I were to normalize, it seems my new table to store the badges would need some sort of role column and then would I need a third lookup table for the role?? An开发者_JAVA技巧d then to complicate things, I need to query for Commitments by Approver_ID using the passed in bound variable 'dept'. Not sure which way to go.
TABLE: commitment_emp
Commitment_ID (PK) (FK) VARCHAR2(10)
badge (PK) VARCHAR2(10)
role (PK) VARCHAR2(20)
Use:
SELECT c.originator_id,
orig.lname,
orig.fname,
c.checker_id,
check.lname,
check.fname,
c.reviewer_id,
review.lname,
review.fname,
c.approver_id,
approve.lname,
approve.fname
FROM COMMITMENT c
LEFT JOIN PB orig ON orig.badge = c.originator_id
LEFT JOIN PB check ON check.badge = c.checker_id
LEFT JOIN PB review ON review.badge = c.reviewer_id
JOIN PB approve ON approve.badge = c.approver_id
AND approve.dept ?
ORDER BY c.commitment_id
JOINs and table aliases are your friends - table design is fine.
精彩评论