WRITE A QUERY

How could I modify the query way down below to report inventory as an "In Stock" at the product level and not the variant level? For example the query below returns: productid,variantid,productname,variantname,categoryname,manufacturer,producttype,image,url,inventory,description,extensiondata,misctext,price p123,v123,widget,blue,categoryA,acme,shirt,[login to view URL],[login to view URL],3,wave shirt,cotton,pre-shrunk,$10 p123,v124,widget,green,categoryA,acme,shirt,[login to view URL],[login to view URL],2,wave shirt,cotton,pre-shrunk,$10 p123,v125,widget,red,categoryA,acme,shirt,[login to view URL],[login to view URL],5,wave shirt,cotton,pre-shrunk,$10 p124,v123,widget,blue,categoryA,acme,shirt,[login to view URL],[login to view URL],3,wave shirt,cotton,pre-shrunk,$10 p124,v124,widget,green,categoryA,acme,shirt,[login to view URL],[login to view URL],2,wave shirt,cotton,pre-shrunk,$10 p124,v125,widget,red,categoryA,acme,shirt,[login to view URL],[login to view URL],5,wave shirt,cotton,pre-shrunk,$10 I'd rather get a result like below where only one line shows for product P123 and it says "In Stock" if any of the variants (color) have any inventory greater than 0 and null for anything out of stock: productid,variantid,productname,variantname,categoryname,manufacturer,producttype,image,url,inventory,description,extensiondata,misctext,price p123,v123,widget,categoryA,acme,shirt,[login to view URL],[login to view URL],IN STOCK,wave shirt,cotton,pre-shrunk,$10 p124,v125,widget,categoryA,acme,shirt,[login to view URL],[login to view URL],[NULL],wave shirt,cotton,pre-shrunk,$10 Here is the current query I use. WITH Categories (ParentCategoryID, CategoryID, Name, ComputedLevel, Sort) AS ( SELECT [login to view URL], [login to view URL], [login to view URL], 0 AS ComputedLevel, CAST('\'+[login to view URL] AS NVARCHAR(255)) FROM Category AS c WHERE ParentCategoryID = 0 AND Deleted=0 AND Published=1 UNION ALL SELECT [login to view URL], [login to view URL], [login to view URL], ComputedLevel + 1, CAST([login to view URL] + '\'+[login to view URL] AS NVARCHAR(255)) FROM Category AS c INNER JOIN Categories AS s ON [login to view URL] = [login to view URL] WHERE Deleted=0 AND Published=1 ) SELECT [login to view URL], [login to view URL], [login to view URL] AS ProductName, [login to view URL] AS VariantName, [login to view URL] AS CategoryName, [login to view URL] AS Manufacturer, '/images/product/icon/' + Convert(varchar(max), [login to view URL]) +'.jpg' as IMAGE, '/p-' + cast([login to view URL] as nvarchar) + '-' + [login to view URL] + '.aspx' as URL, [login to view URL], [login to view URL], [login to view URL], [login to view URL], [login to view URL] FROM ProductVariant AS pv WITH (NOLOCK) JOIN Product AS p WITH (NOLOCK) ON [login to view URL] = [login to view URL] JOIN ProductCategory AS pc WITH (NOLOCK) ON [login to view URL] = [login to view URL] JOIN ProductManufacturer AS pm WITH (NOLOCK) ON [login to view URL] = [login to view URL] JOIN Manufacturer AS m WITH (NOLOCK) ON [login to view URL] = [login to view URL] JOIN Categories AS c WITH (NOLOCK) ON [login to view URL] = [login to view URL] WHERE pv.Deleted=0 AND pv.Published=1 AND p.Deleted=0 and pv.Published=1