>>Try
>>

>>select TABLE1.*, NVL(C_USED.USED_QUANTITY,0) AS USED_QUANTITY FROM TABLE1 
>>  LEFT JOIN (select TABLE2.PART_NO, NVL(SUM(TABLE2.USED_QTY),0) as USED_QUANTITY, 
>>      FROM TABLE2 GROUP BY TABLE2.PART_NO) C_USED 
>>      ON TABLE1.PART_NO = C_USED.PART_NO 
>>AND (c_used.USED_QUANTITY < 10 or c_Used.Used_Quantity IS NULL)
>>

>
>I thought about it too (the fact that the name could be confusing). But somehow, it does select all records with QTY greater than 0 but not equal to 0. But I will try your suggestion too.
>
>Thank you.

By adding your AND condition you automatically exclude NULL records, thus you don't get them in your result. By adding an OR condition I attempt to resolve this problem. Or you can use NVL(c_Used.Used_Quantity,0) less than 10 again in the AND condition, but I think OR should perform better.