SQL多条件过滤左连接查询未返回预期结果问题排查

Let's break down the critical issues in your current SQL and walk through the correct solutions to get the results you need:

What's Wrong With the Original Query? #

• Impossible join logic: Your ON clause uses multiple AND conditions that can never be true simultaneously. The Filter table's Parameter column can't hold four different values ('Sales', 'International sales', 'New York', 'AMAR') for a single row. This means your LEFT JOIN will never match any rows from SampleData.
• Incorrect field mapping: You're hardcoding mismatched relationships between Filter and SampleData. For example, you link the Country condition's parameter ('New York') to SampleData.Location instead of SampleData.Country. You also aren't using the Filter.Condition column to dynamically match the correct fields in SampleData.
• Filter table data mismatch: Your Filter table has Country set to 'New York', but your requirement is to filter for Country = 'USA'. This data inconsistency needs fixing if you want to use the filter table directly.

Correct Solutions #

Solution 1: Direct Filter (Simplest Approach) #

If you don't need to rely on the Filter table and just want to get records matching your stated requirements, skip the join entirely and use a straightforward WHERE clause:

SELECT *
FROM SampleData
WHERE Department = 'Sales'
  AND Division = 'International Sales' -- Note the capital 'S' (matches your SampleData values)
  AND Place = 'AMAR'
  AND Country = 'USA';

This query will return rows 2-5 from your SampleData table, which exactly meet your criteria.

Solution 2: Dynamic Filter Using the Filter Table #

If you need to base your filter on the Filter table (for reusable conditions), first fix the 4th row of the Filter table to set Parameter = 'USA' (to align with your requirement). Then use this query:

SELECT sd.*
FROM SampleData sd
WHERE EXISTS (
    SELECT 1
    FROM Filter f
    WHERE 
        (f.Condition = 'Department' AND sd.Department = f.Parameter)
        OR (f.Condition = 'Division' AND sd.Division = f.Parameter)
        OR (f.Condition = 'Place' AND sd.Place = f.Parameter)
        OR (f.Condition = 'Country' AND sd.Country = f.Parameter)
    GROUP BY sd.Id
    HAVING COUNT(DISTINCT f.Condition) = (SELECT COUNT(DISTINCT Condition) FROM Filter)
);

The subquery verifies that a SampleData row matches every unique condition in the Filter table, ensuring all your criteria are met.

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