mysql - 在列中重复外键

Question

您好，我有这个数据库表，例如

-transaction number- pk
-id_number- fk from employees table
-request- 
-department_request_status-
-department_oic- fk from id_number of employees table again
-headoffice
-headoffice_oic- fk from id_number of employees table again

例如，我可以左加入他们然后在表中显示三个 id 所有者的名字吗？

例如

SELECT 
            table.ID_NUM,
            table.DEP_OIC,
            table.HeadOffc_OIC
            employees.LNAME, ===>select this for ID_NUM,DEP_OIC, and HeadOffc_OIC
            employees.FNAME  ===>select this for ID_NUM,DEP_OIC, and HeadOffc_OIC
        FROM leave_application
        LEFT JOIN employees
        WHERE leave_application.ID_NUM = employees.ID_NUM
        LEFT JOIN employees
        WHERE leave_application.DEP_OIC = employees.ID_NUM

我怎样才能正确地做到这一点？是否可以？或者我必须将它们分成三张表？？

Answer 1

您可以分别将每个 EmployeeID(id_number、department_oic、headoffice_oic)与 leave_application 表连接起来，并将每个员工视为一个表，然后将所有结果与 leave_application 左连接，现在您将所有三个所有者的姓名都放在一行中:

    select leave_application.tranid, HeadOffFName, HeadOffLName, DepFName, DepLName, FName, LName from leave_application 
LEFT JOIN
    (select TranID, FName as HeadOffFName, LName as HeadOffLName from 
    Employee inner join leave_application
    on Employee.EmpID = leave_application.HeadOffc_OIC)Head
    ON leave_application.TranID = Head.tranid
left join
    (select TranID, FName as DepFName, LName as DepLName from 
    Employee inner join leave_application
    on Employee.EmpID = leave_application.DEP_OIC)Dep
ON leave_application.TranID = Dep.tranid
left join
    (select TranID, FName, LName from 
    Employee inner join leave_application
    on Employee.EmpID = leave_application.ID_NUM)Office
ON leave_application.TranID = Office.tranid