查询参与East London Crossing项目的已婚员工及配偶：仅用JOIN可行吗？

You don't need any additional operations beyond JOINs to retrieve the required data. Here's how to approach it, step by step:

Logical Breakdown of Required Joins #

To get the married employees assigned to the "East London Crossing" project and their spouses' names, you need to connect the tables in sequence:

1. Project: Filter to get the specific project's ProjectNumber using its name.
2. Workon: Link to Project to find all employees assigned to that project.
3. Employee: Join with Workon to fetch the employee's details (including their name).
4. Marriage: Connect to Employee to identify which of these employees are married and get their spouse's EmployeeNumber.
5. Employee (again): Join with Marriage a second time to retrieve the spouse's name using their EmployeeNumber.

Example SQL Query #

SELECT 
    emp.EmployeeNumber AS EmployeeID,
    emp.Name AS EmployeeName,
    spouse.Name AS SpouseName
FROM 
    Project p
JOIN 
    Workon w ON p.ProjectNumber = w.ProjectNumber
JOIN 
    Employee emp ON w.EmployeeNumber = emp.EmployeeNumber
JOIN 
    Marriage m ON emp.EmployeeNumber = m.EmployeeNumber
JOIN 
    Employee spouse ON m.spouseNumber = spouse.EmployeeNumber
WHERE 
    p.ProjectName = 'East London Crossing';

Key Notes #

• We use inner joins here because we only want employees who meet all criteria: assigned to the target project AND married (with a spouse present in the Employee table).
• The defined foreign key constraints (FK_Workon-employee, FK_Workon-project, Fk_Marriage-employee) ensure referential integrity, so we don't have to worry about invalid or missing records breaking the joins.

内容的提问来源于stack exchange，提问作者user8688087