Sql-server – How to select an item from a column only if none of the value in corresponding column is NULL

sql server

I want to know if I can assemble a part.( Which is possible if I have all the raw material). So from the Table I want to select the item number only if Quantity for raw material is NOT NULL (not zero).

The table is like this
enter image description here

So I want the result AS

Model 2
Model 3
Model 4

The query I am using is

SELECT DISTINCT ItemNumber FROM TABLE WHERE QUANTITY IS NOT NULL

But because in 2 rows Model 1 have some quantity which is not NULL I am getting that in result as well.
Please let me know a way of fixing it.

Best Answer

you can use NOT IN and a subselect

CREATE TABLE modelitem
    ([ltemNumber] varchar(7), [Cornponent] varchar(6), [Quantity] varchar(4))
;
    
INSERT INTO modelitem
    ([ltemNumber], [Cornponent], [Quantity])
VALUES
    ('Modell', 'Wheel', '19'),
    ('Modell', 'Bolt', '19'),
    ('Modell', 'Engine', NULL)
    ,('Modell', 'Engine', NULL),
    ('Model 2', 'Wheel', '19'),
    ('Model 2', 'Bolt', '19'),
    ('Model 2', 'Engine', '6'),
    ('Model 3', 'Wheel', '19'),
    ('Model 3', 'Bolt', '19'),
    ('Model 3', 'Engine', '6'),
    ('Mode14', 'Wheel', '19'),
    ('Mode14', 'Bolt', '19'),
    ('Mode14', 'Engine', '6')
;
GO

SELECT DISTINCT [ltemNumber] FROM modelitem WHERE [ltemNumber] NOT IN (SELECT DISTINCT [ltemNumber] FROM modelitem WHERE [Quantity] IS NULL)
GO

| ltemNumber |
| :--------- |
| Mode14     |
| Model 2    |
| Model 3    |

db<>fiddle here

Related Question