SQL Server 2012

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Product (Id INT NOT NULL, UseMultipleWarehouses int, StockQuantity int,
CONSTRAINT [PK_product] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO PRODUCT (Id, UseMultipleWarehouses, StockQuantity)
            VALUES   (1, 0, 10)
			         ,(2, 0, 12)
					 ,(3, 1, 14)
					 ,(4, 1, 15)
					 
select * from [Product]


Id
UseMultipleWarehouses
StockQuantity


1
0
10

2
0
12

3
1
14

4
1
15


CREATE TABLE ProductWarehouseInventory (Id INT NOT NULL, StockQuantity int, ProductId int, ReservedQuantity int,
CONSTRAINT [PK_ProductWarehouseInventory] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[ProductWarehouseInventory]  WITH CHECK ADD  CONSTRAINT [FK_ProductWarehouseInventory_product] FOREIGN KEY([ProductId])
REFERENCES [dbo].[Product] ([id])

ALTER TABLE [dbo].[ProductWarehouseInventory] CHECK CONSTRAINT [FK_ProductWarehouseInventory_product]


INSERT INTO ProductWarehouseInventory (Id, StockQuantity, ProductId, ReservedQuantity)
            VALUES   (1, 100, 1, 10)
			        ,(2, 200, 2, 20)
					,(3, 300, 3, 30)
					,(4, 400, 4, 390)
					
select * from [ProductWarehouseInventory]


Id
StockQuantity
ProductId
ReservedQuantity


1
100
1
10

2
200
2
20

3
300
3
30

4
400
4
390


select * from Product p
    where p.Id != 0
    AND  
    (
        (p.UseMultipleWarehouses = 0 AND p.StockQuantity = 10)
        OR
        (p.UseMultipleWarehouses != 0 AND
            EXISTS (SELECT CASE WHEN count([pwi].ProductId) > 0 THEN count([pwi].ProductId) ELSE NULL END  FROM ProductWarehouseInventory [pwi]
            WHERE ([pwi].ProductId = p.Id) --) commented here
            GROUP BY [pwi].ProductId --p.Id
            HAVING (SUM([pwi].StockQuantity) - SUM([pwi].ReservedQuantity)) = 10
          ) -- added here
		)
	)


Id
UseMultipleWarehouses
StockQuantity


1
0
10

4
1
15