多表查询SQL需求：从3张表中获取每个客户的唯一记录

Got it, let's work through how to retrieve that single unique record per customer using your three tables. Here's a step-by-step solution tailored to your needs:

First, Clarify Table Roles #

Let's recap the purpose of each table to make sure we're aligned:

• Account Master: Stores active customers – we need to confirm a customer exists here to count them as active.
• MeterStatus: Holds customers who are registered in the system but haven't reached active status yet.
• Codes.MeterStatus: A lookup table for meter status codes (it likely maps raw status IDs to human-readable labels, right?).

Core Query Logic #

Our goal is to combine data from active and registered-but-not-active customers, ensure only one record per customer, and pull in meaningful status labels from the lookup table. Here are two solid approaches:

Approach 1: Using UNION (Clean, for mutually exclusive customer sets) #

If you're confident a customer can't be in both Account Master and MeterStatus at the same time, this is the simplest way. UNION automatically removes duplicates if any slip through:

SELECT 
    CustomerID,
    CustomerName,
    cms.StatusLabel,
    'Active' AS CustomerStatusCategory
FROM [Account Master] am
JOIN Codes.MeterStatus cms 
    ON am.MeterStatusCode = cms.StatusCode

UNION

SELECT 
    CustomerID,
    CustomerName,
    cms.StatusLabel,
    'Registered - Not Active' AS CustomerStatusCategory
FROM MeterStatus ms
JOIN Codes.MeterStatus cms 
    ON ms.MeterStatusCode = cms.StatusCode
ORDER BY CustomerID;

Approach 2: Using FULL OUTER JOIN (Safeguard for overlapping data) #

If there's a chance a customer might appear in both tables (even accidentally), use this method to prioritize active customer data and avoid duplicates:

SELECT 
    COALESCE(am.CustomerID, ms.CustomerID) AS CustomerID,
    COALESCE(am.CustomerName, ms.CustomerName) AS CustomerName,
    cms.StatusLabel,
    CASE 
        WHEN am.CustomerID IS NOT NULL THEN 'Active'
        ELSE 'Registered - Not Active'
    END AS CustomerStatusCategory
FROM 
    (SELECT CustomerID, CustomerName, MeterStatusCode FROM [Account Master]) am
FULL OUTER JOIN 
    (SELECT CustomerID, CustomerName, MeterStatusCode FROM MeterStatus) ms
    ON am.CustomerID = ms.CustomerID
LEFT JOIN 
    Codes.MeterStatus cms
    ON COALESCE(am.MeterStatusCode, ms.MeterStatusCode) = cms.StatusCode
WHERE 
    am.CustomerID IS NOT NULL OR ms.CustomerID IS NOT NULL
ORDER BY 
    CustomerID;

Key Notes for Customization #

• Use COALESCE() to pull data from Account Master first if a customer exists in both tables (prioritizing active status data).
• Add more fields to the SELECT clause as needed – just wrap shared fields in COALESCE to handle cases where they might exist in both tables.
• The CASE statement makes it instantly clear whether a customer is active or just registered.

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