mysql - 同一项目中的员工和经理？数据库

Question

我做了一些挖掘，但无法在脑海中安排...我的问题是找到同一个项目中的员工和经理？我有 2 个表，它们是 Employee 和 Project。他们喜欢；

   *Employee*               
   ----------               
   employee_id (PK)          
   name
   manager_id (FK)   ref employee_id in same table which is employee_id.
   usertype    

   *Project*
   ----------
   project_id(PK)
   project_name
   employee_id(FK)   ref employee_id in emplyee table
   manager_id(FK)    ref employee_id in emplyee table

让我更具体一点。项目必须有 1 名经理和几名员工。我怎样才能写查询？ usertype 列指定此员工 MANAGER OR USER(普通员工)。

查询运行示例:

       ______    ______________    ____________
       |name|    |manager_name|   |project_name|  
        John         Susan       CalendarIphone
        Mike         Susan       CalendarIphone
        Joe         Patrick        AndoridApp
        Megan        Susan        CalendarIphone
        Melek       Patrick        AndoridApp

如您所见，Mike 可以在多个项目中工作，并且可以拥有多个经理。但经理只能拥有 1 个项目。那么我应该怎么做才能得到这个呢？提前致谢。

编辑-------------------------------------------- ----------------------------------

我有一个包含下拉列表的项目。我在这里选择了一个项目，它根据该项目向我显示了一个表格，并列出了该项目的经理，该项目的员工。我要求项目名称再创建一个列，您可以忽略它。我只想看到它的经理和里面的员工。

编辑 2-------------------------------------------- --------------

例如项目 ID = 2，在这个项目中我们有 1 名经理和员工。

   |name|    |manager_name|    
    John         Susan      
    Mike         Susan       
    Joe          Susan       
    Megan        Susan       
    Melek        Susan

Answer 1

你的架构不好。它应该是这样的:

   Employee*               
   ----------               
   employee_id (PK)          
   name
   manager_id (FK)   ref employee_id in employee table

   *Project*
   ----------
   project_id(PK)
   project_name
   manager_id(FK)    ref employee_id in employee table

   *Project_Employee*
   ----------
   project_id(FK)  ref project_id in project table
   employee_id(FK) ref employee_id in employee table

然后你可以这样做:

   select
       e.name as name,
       em.name as manager_name
       p.project_name
       pm.name as project_manager_name
   from Employee as e
       left outer join Employee as em on em.employee_id = e.manager_id
       left outer join Project_Employee as pe on pe.employee_id = e.employee_id
       left outer join Project as p on p.project_id = pe.project_id
       left outer join Employee as pm on pm.employee_id = p.manager_id

但是对于每个员工可能有多个项目，员工可以在这个查询中重复

更新

 select
     e.name, u.is_manager
 from (
     select employee_id, 0 as is_manager from Project_Employee as pe where pe.idproject_id= <project id>
     union all
     select manager_id, 1 as is_manager from Project as p where p.project_id= <project id>
 ) as u
     inner join Employee as e on e.employee_id = u.employee_id
 order by u.is_manager desc

更新 2

select
    e.name as employee_name,
    pm.name as manager_name
from Project_Employee as pe
    left outer join Employee as e on e.employee_id = pe.employee_id
    left outer join Project as p on p.project_id = pe.project_id
    left outer join Employee as pm on pm.employee_id = p.manager_id
where pe.project_id = <project id>