Sub Query - Part Numbers and Quantity

Question

Looking to find all part numbers (d046d) with at least one record having vaule greater than zero.

d046d                        e024a
ABC123                       0
ABC123                       0
ABC123                       0
123ABC                       0
123ABC                       1
123ABC                       0
1A2B3C                       0
1A2B3C                       0

All I want returned is 123ABC

SELECT d008g, d046d, e024a
FROM 20121
WHERE (20121.[d046d])=(select sc.d046d from 20121 as sc where e024a >0)

This will error becuase it will find multiple d046d in the subquery.

Answer 1

If what you want is just those parts with a quantity greater than zero, then your where clause should probably just say

WHERE e024a > 0

Now you say you want part numbers where at least one record is > 0, so then I can conclude that you only want to see each qualifying part number once - this is best achieved by using DISTINCT:

SELECT DISTINCT d008g, d046d, e024a
FROM 20121
WHERE e024a > 0

Answer 2

You can use an In Operator. In Operator Reference

SELECT d008g, d046d, e024a
FROM 20121
WHERE (20121.[d046d]) In (select sc.d046d from 20121 as sc where e024a >0)