How to filter records using subquery results in SQL

Data and Databases
1

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?

2

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?

3

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.

4

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.