多表联合查询求助（SQL/MS Access）：获取匹配去重数据

Hey Arun, let's break down why your query is returning duplicates and fix it up:

The Core Issue #

Your original query was missing a critical join condition: you didn't link Project_Phase_Selection and Complexity_Matrix by the Project field. This caused a Cartesian product—where phase data from one project was incorrectly paired with function/complexity data from another project—creating tons of invalid, duplicate-looking records.

On top of that, your column order and ORDER BY clause needed tweaks to match your desired output.

Corrected Query #

SELECT 
    cm.Project,
    cm.Function,
    pps.Month,
    pps.Phase,
    cm.Complexity,
    cds.Needed
FROM 
    Project_Phase_Selection pps
INNER JOIN 
    Complexity_Matrix cm ON pps.Project = cm.Project
INNER JOIN 
    Complexity_Data_Sheet cds ON pps.Phase = cds.Phase 
                              AND cm.Function = cds.Function 
                              AND cm.Complexity = cds.Complexity
ORDER BY 
    cm.Project,
    cm.Function,
    pps.Month,
    pps.Phase;

Key Fixes & Improvements #

1. Added Project Join Condition: The pps.Project = cm.Project line ensures we only pair phase data with function/complexity data from the same project—eliminating cross-project invalid combinations that caused duplicates.
2. Aligned Column Order: Matches your requested sequence: Project, Function, Month, Phase, Complexity, Needed.
3. Simplified Table Aliases: Shortened table names to pps, cm, cds for readability.
4. Cleaned Up Sorting: Removed duplicate fields in ORDER BY and sorted by logical, relevant fields to keep results organized.
5. Removed Unnecessary DISTINCT: With correct joins, each combination of Project, Function, Month, Phase will only return one valid Needed value. If your real data has duplicate source records, you can re-add DISTINCT to the SELECT clause.

This query will return exactly the matched, non-duplicate data you're looking for, aligned with your expected structure.

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