declare @ProductLine table

(ProductlineID int)

declare @Products table

(ProductLineID int,

ProductID int,

Status int)

insert into @ProductLine values (1)

Insert into @ProductLine values (2)

Insert into @ProductLine values (3)

Insert into @ProductLine values (4)

Insert into @Products values (1,100, 1)

insert into @Products values (1,200, 2)

Insert into @Products values (1,300, 1)

insert into @Products values (1,400, 2)

Insert into @Products values (2,1000, 1)

insert into @Products values (2,2000, 1)

Insert into @Products values (2,3000, 1)

insert into @Products values (3,10, 2)

insert into @Products values (4,1, 2)

insert into @Products values (4,2, 2)

-- find all product lines with all products out of stock. A product is out of stock has status=2

-- From input above, product lines 3 and 4 will be reported.

SELECT

a.ProductLineID,

Productsoutofstock = SUM(CASE b.Status WHEN 2 THEN 1 ELSE 0 END)

FROM

@ProductLine a JOIN @Products b ON

a.ProductlineID = b.ProductLineID

WHERE

NOT EXISTS (SELECT 1 FROM @Products c WHERE a.ProductlineID = c.ProductLineID AND c.status != 2)

GROUP BY

a.ProductlIneid