DB2 SQL基于子查询结果过滤Legsum表记录的问题求助

Hey there! Let’s work through why only your first condition is taking effect when filtering your Legsum (LS) table with a subquery—this is such a common hiccup when getting up to speed with SQL, so we’ll get it sorted out.

Common Reasons & Fixes #

Let’s break down the most likely issues and how to fix them:

1. Mixed-Up Logical Operators (AND/OR)

A super frequent mistake is using OR when you actually need AND (or vice versa) in your subquery. If you use OR, only the first condition might be matching records that don’t meet the others, making it seem like the rest aren’t working.

Wrong Example (using OR when you need all conditions met):

SELECT *
FROM Legsum LS
WHERE LS.id IN (
    SELECT id
    FROM YourRelatedTable
    WHERE condition1 = 'target_value1'
    OR condition2 = 'target_value2'  -- This lets records pass if only condition1 is true
    OR condition3 = 'target_value3'
    OR condition4 = 'target_value4'
);

Fixed Version (using AND for all required conditions):

SELECT *
FROM Legsum LS
WHERE LS.id IN (
    SELECT id
    FROM YourRelatedTable
    WHERE condition1 = 'target_value1'
    AND condition2 = 'target_value2'  -- Now all 4 conditions must be true
    AND condition3 = 'target_value3'
    AND condition4 = 'target_value4'
);

2. Missing Link Between Subquery and Main Table

If your subquery doesn’t properly connect to the Legsum table, the extra conditions might not apply to the rows you’re trying to filter. This often happens with EXISTS subqueries.

Wrong Example (no association to LS table):

SELECT *
FROM Legsum LS
WHERE EXISTS (
    SELECT 1
    FROM YourRelatedTable
    WHERE condition1 = 'target_value1'
    AND condition2 = 'target_value2'  -- This checks all rows in the related table, not just those linked to LS
    AND condition3 = 'target_value3'
    AND condition4 = 'target_value4'
);

Fixed Version (add a link to the main table):

SELECT *
FROM Legsum LS
WHERE EXISTS (
    SELECT 1
    FROM YourRelatedTable RT
    WHERE RT.legsum_id = LS.id  -- This ties the subquery to the current LS row
    AND RT.condition1 = 'target_value1'
    AND RT.condition2 = 'target_value2'
    AND RT.condition3 = 'target_value3'
    AND RT.condition4 = 'target_value4'
);

3. NULL Values Breaking Condition Checks

If any of your condition columns can have NULL values, using = to compare will fail (since NULL = anything is never true). You’ll need to handle these explicitly.

Example Handling NULLs:

SELECT *
FROM Legsum LS
WHERE LS.id IN (
    SELECT id
    FROM YourRelatedTable
    WHERE condition1 = 'target_value1'
    AND condition2 = 'target_value2'
    AND (condition3 = 'target_value3' OR condition3 IS NULL)  -- Account for possible NULL
    AND condition4 = 'target_value4'
);

If you can share your actual SQL code, we can pinpoint the exact issue even faster—but these fixes cover most cases where only the first condition works!

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