SQL NOT EXISTS查询返回重复值且结果不符预期的解决咨询

Hey there, let's sort out your SQL issue—you're trying to find records in [BO Data] that don't have a matching Keyorderstatus in [Order Data], but right now your query is doing the opposite (and spitting out duplicates). Here's what's wrong and how to fix it:

1. The Critical NOT EXISTS Logic Error #

Your subquery is referencing [BO Data] instead of [Order Data]—that means you're checking if a record doesn't exist in the same table, which is why all your results are actually present in [Order Data] (the condition is basically always false, so it's returning all rows that match your OrderStatusCode filter).

The correct NOT EXISTS syntax should compare [BO Data] to [Order Data] in the subquery.

2. Fixing the Duplicate Results #

Duplicates usually happen if [BO Data] has multiple rows with the same Keyorderstatus (or duplicate rows overall). You can handle this either by using DISTINCT to return unique records, or first cleaning up duplicate entries in [BO Data] if that's unintended.

Corrected Query (with Duplicate Handling) #

Here's the fixed version that does what you intended:

SELECT DISTINCT  -- Add DISTINCT to eliminate duplicates
    Keyorderstatus, 
    OrderNumber, 
    PartsNo, 
    HoldType, 
    ShiptoCode, 
    BackOrderQty, 
    OrderStatusCode 
FROM [BO Data] 
WHERE 
    OrderStatusCode = "AWAITING..."
    AND NOT EXISTS (
        SELECT 1  -- Using SELECT 1 is more efficient than SELECT *
        FROM [Order Data] 
        WHERE [Order Data].Keyorderstatus = [BO Data].Keyorderstatus
    )

What Changed? #

• Swapped [BO Data] for [Order Data] in the subquery to correctly check for missing matches
• Added DISTINCT to remove duplicate rows from the result set
• Used SELECT 1 instead of SELECT * in the subquery (it's faster since we don't need to fetch all columns)

If you don't want to use DISTINCT, you could also investigate why [BO Data] has duplicates—maybe there's a missing primary key or a data import issue that needs fixing at the source.

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