How to filter records using subquery results in SQL

I’m working on a database project that shows pending orders from OrderTable. There’s also a second table called TruckTable that stores percentage values for each order showing how much has been loaded. A third table LocationTable contains shipping destination info.

SELECT
    OT.OrderNumber, OT.LineItem, OT.OrderStatus, OT.RequiredDate,
    (SELECT SUM(loadpercentage) AS TotalLoaded
     FROM TruckTable TT
     WHERE TT.OrderNumber = OT.OrderNumber
       AND TT.LineItem = OT.LineItem) AS TotalLoaded
FROM
    OrderTable OT
INNER JOIN 
    LocationTable LT ON OT.ShipTo = LT.CustomerID
WHERE
    OT.OrderStatus <= 'E'
    AND LT.Territory = '08'
    AND (OT.RequiredDate >= '6/1/2025' AND OT.RequiredDate <= '6/7/2025')

The subquery calculates total percentage already loaded on trucks for each order. I need to modify this to only display orders where the total percentage is less than 100%. Some orders might have zero records in the truck table (0% loaded) while others could have partial loading. How can I add this condition to filter out fully loaded orders?

Interesting challenge! Try wrapping your query in a CTE or derived table - then you can filter on TotalLoaded after it’s calculated. Or use HAVING SUM(loadpercentage) < 100 if you’re grouping by order fields. What happens with orders that don’t have truck records? Do they show as null or zero?

Move your subquery to the WHERE clause and use COALESCE to handle null values when orders don’t have truck records. Replace your current WHERE conditions with this:

WHERE
    OT.OrderStatus <= 'E'
    AND LT.Territory = '08'
    AND (OT.RequiredDate >= '6/1/2025' AND OT.RequiredDate <= '6/7/2025')
    AND COALESCE((SELECT SUM(loadpercentage) 
                  FROM TruckTable TT 
                  WHERE TT.OrderNumber = OT.OrderNumber 
                    AND TT.LineItem = OT.LineItem), 0) < 100

COALESCE makes sure orders with no truck records return 0 instead of null, so they’ll show up as unloaded orders. This keeps your existing query structure but adds the percentage filter you’re looking for.

you could also try a left join instead of subqueries. join your ordertable with a grouped trucktable on ordernumber and lineitem, then filter where the sum’s under 100 or null. might run faster than repeated subqueries depending on how much data ur working with.