多ON条件SQL语句失效，求基于DPA值过滤联系人数据的解决方案

Hey John, let's work through this problem step by step. It sounds like you're trying to build a query that filters and redacts contact details based on specific DPA values, plus you're hitting snags with multiple ON conditions in your SQL. Let's break down your requirements and build a working query together.

First, let's restate your rules clearly to make sure we're aligned:

• Exclude any contact where DPA.value = 5
• Exclude contacts where DB.OrganisationPersonnel.EmployedToDate IS NULL
• Hide email address if DPA.value is 3 or 4 (return null/empty instead of the actual email)
• Hide phone number if DPA.value is 2 or 4 (same logic for phone)

Common Pitfall with Multiple ON Conditions #

A lot of folks try to cram all filtering logic into JOIN ON clauses, but that's usually not the right approach—ON is for defining how tables relate to each other, while WHERE is for filtering rows after the join. This mix-up is probably why your original query isn't running correctly.

Working Query Example #

Assuming your OrganisationPersonnel table links to the DPA table via a shared key like PersonID (adjust this if your schema uses a different key), here's a query that implements all your rules:

SELECT 
    op.OrganisationID,
    op.PersonID,
    op.FirstName,
    op.LastName,
    -- Redact email if DPA is 3 or 4
    CASE 
        WHEN dpa.value IN (3, 4) THEN NULL 
        ELSE op.EmailAddress 
    END AS EmailAddress,
    -- Redact phone if DPA is 2 or 4
    CASE 
        WHEN dpa.value IN (2, 4) THEN NULL 
        ELSE op.PhoneNumber 
    END AS PhoneNumber
FROM 
    DB.OrganisationPersonnel op
INNER JOIN 
    DB.DPA dpa ON op.PersonID = dpa.PersonID -- Replace with your actual join key
WHERE 
    dpa.value != 5 -- Exclude contacts with DPA = 5
    AND op.EmployedToDate IS NOT NULL -- Exclude contacts with no EmployedToDate

Key Explanations: #

1. Redaction with CASE Statements: We use CASE to dynamically replace sensitive fields with NULL when the DPA value matches your rules. If you prefer empty strings instead of NULL, just swap NULL for ''.
2. Filtering in WHERE: By putting the exclusion rules in WHERE instead of ON, we keep the join logic clean and ensure we only return contacts that meet all your criteria.
3. Handling One-to-Many Relationships: If a single contact has multiple DPA records, you might get duplicate rows. To fix this, wrap the DPA table in a subquery to get the correct value per contact (e.g., MAX(value) or the most recent record, depending on your business needs):

   JOIN (
       SELECT PersonID, MAX(value) AS value 
       FROM DB.DPA 
       GROUP BY PersonID
   ) dpa ON op.PersonID = dpa.PersonID
   

If You Need LEFT JOIN (For Contacts Without DPA Records) #

If you want to include contacts who don't have a DPA record (and apply your exclusion rules), adjust the query to use LEFT JOIN and add an extra check in WHERE:

SELECT 
    op.OrganisationID,
    op.PersonID,
    op.FirstName,
    op.LastName,
    CASE 
        WHEN dpa.value IN (3, 4) THEN NULL 
        ELSE op.EmailAddress 
    END AS EmailAddress,
    CASE 
        WHEN dpa.value IN (2, 4) THEN NULL 
        ELSE op.PhoneNumber 
    END AS PhoneNumber
FROM 
    DB.OrganisationPersonnel op
LEFT JOIN 
    DB.DPA dpa ON op.PersonID = dpa.PersonID
WHERE 
    (dpa.value != 5 OR dpa.value IS NULL) -- Include contacts with no DPA or DPA !=5
    AND op.EmployedToDate IS NOT NULL

Just make sure to swap out PersonID with whatever actual key links your two tables, and you should be good to go.

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