如何从SQL查询中剔除部分记录？附未完成多表查询语句

Hey there! Let's work through how to filter out unwanted records from your SQL results. First, I noticed your query got cut off at LEFT JOIN StockDetails sd ON pd.It—I’ll fill in logical join conditions (like matching item IDs) for the examples below, since you’re selecting columns from Items and Units too.

First, here’s the cleaned-up, complete version of your base query (with assumed join logic):

SELECT DISTINCT 
    0 AS IID, 
    pd.IID AS PurchaseOrerDetailsId, 
    i.[Description] AS ITEM, 
    '' AS BatchNo, 
    s.[Description] AS Unit, 
    '' AS MfgDt, 
    0 AS QtyReceived, 
    '' AS ExpiryDate, 
    '' AS PackSize, 
    0 AS QtyOrdered, 
    0 AS MRP, 
    0 AS PTR, 
    0 AS PTS, 
    0 AS PurchaseRate, 
    0 AS CGST, 
    0 AS SGST, 
    0 AS IGST, 
    0 AS DiscPer, 
    0 AS DiscVal, 
    0 AS PurchaseValue, 
    0 AS CGSTAmt, 
    0 AS SGSTAmt, 
    i.IID AS ItemId 
FROM PurchaseOrderDetails pd 
LEFT JOIN StockDetails sd ON pd.ItemId = sd.ItemId -- Assumed join on item ID
LEFT JOIN Items i ON pd.ItemId = i.IID -- Join to get item description
LEFT JOIN Units s ON pd.UnitId = s.IID -- Join to get unit description

Now, here are the most practical ways to exclude records from this result set:

1. Filter Specific Values with a WHERE Clause #

If you know exactly which records to exclude (e.g., items with zero ordered quantity, or specific item IDs), add a WHERE clause at the end:

-- Example 1: Exclude purchase orders with zero ordered quantity
SELECT DISTINCT 
    -- Your selected columns here
FROM PurchaseOrderDetails pd 
LEFT JOIN StockDetails sd ON pd.ItemId = sd.ItemId
LEFT JOIN Items i ON pd.ItemId = i.IID
LEFT JOIN Units s ON pd.UnitId = s.IID
WHERE pd.QtyOrdered != 0;

-- Example 2: Exclude specific item IDs
WHERE i.IID NOT IN (105, 203, 301); -- Replace with your target IDs

2. Exclude Records That Exist in Another Table (Using NOT EXISTS) #

If you want to exclude purchase orders that already have a matching entry in StockDetails (like already received items), use NOT EXISTS:

SELECT DISTINCT 
    -- Your selected columns here
FROM PurchaseOrderDetails pd 
LEFT JOIN Items i ON pd.ItemId = i.IID
LEFT JOIN Units s ON pd.UnitId = s.IID
WHERE NOT EXISTS (
    SELECT 1 
    FROM StockDetails sd 
    WHERE sd.ItemId = pd.ItemId -- Match on your key column
);

This returns only purchase order details with no corresponding stock entry.

3. Use LEFT JOIN + IS NULL to Exclude Matched Records #

A common alternative to NOT EXISTS is using a left join and filtering where the joined table’s key is null. This works well if you need to reference columns from the joined table in other parts of your query:

SELECT DISTINCT 
    -- Your selected columns here
FROM PurchaseOrderDetails pd 
LEFT JOIN StockDetails sd ON pd.ItemId = sd.ItemId
LEFT JOIN Items i ON pd.ItemId = i.IID
LEFT JOIN Units s ON pd.UnitId = s.IID
WHERE sd.IID IS NULL; -- Exclude records that have a match in StockDetails

4. Remove Duplicates Beyond DISTINCT #

If DISTINCT isn’t enough (e.g., you need to keep only the latest purchase order per item), use a window function like ROW_NUMBER() to rank records and exclude duplicates:

WITH RankedPurchaseOrders AS (
    SELECT 
        -- Your selected columns here,
        ROW_NUMBER() OVER (PARTITION BY pd.ItemId ORDER BY pd.CreatedDate DESC) AS rn
    FROM PurchaseOrderDetails pd 
    LEFT JOIN StockDetails sd ON pd.ItemId = sd.ItemId
    LEFT JOIN Items i ON pd.ItemId = i.IID
    LEFT JOIN Units s ON pd.UnitId = s.IID
)
SELECT * FROM RankedPurchaseOrders WHERE rn = 1; -- Keep only the latest record per item

Just adjust the join conditions, filter values, and window function logic to match your actual table schema and the exact records you want to exclude!

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