mysql - 列出分配到“东伦敦十字路口”项目的所有已婚员工及其配偶的姓名

Question

Project   Table Pk(project Number)

 Marriage Table : PK (EmployeeNumber)
 Fk_ Marriage - employee (spouseNumber) 

Employee Table (EmployeeNumber) 

Workon Table : PK(EmployeeNumber,ProjectNumber,CityCode) 
              FK_ Workon - employee (EmployeeNumber)
              FK_ Workon - project (projectNumber)

1 Table : Project  pk(project Number)
2 Table : Marriage PK (EmployeeNumber) 
          Fk_ Marriage - employee (spouseNumber)
3 Table : Employee(EmployeeNumber) 
4 Table : Workon PK(EmployeeNumber,ProjectNumber,CityCode) 
          FK_ Workon - employee (EmployeeNumber)
          FK_ Workon - project (projectNumber)

   List the names of all married employees and their spouses who are assigned to the “East London Crossing” project

加入是可能的，或者我们需要做的不仅仅是加入爱情问题

标签： mysqljoinself-join

Answer 1

Andrew Fan 我修改了你的代码有多好

SELECT E.EmployeeNumber,E.FirstName, E.LastName,
       E.SpouseNumber, w.projectNumber,P.name
       FROM Project P, Workon W, Employee E 
       WHERE P.projectNumber = W.projectNumber AND
             W.employeeNumber = E.EmployeeNumber  
             AND P.name = "East London Crossing" AND 
                 E.SpouseNumber is not NULL;