CODEWITHSUNDEEP
sqlsql-serverdatabasestored-procedures
hsobhy
hsobhy

Reputation: 1523

MS SQL conditional stored procedure to return values in all cases

I have MS SQL Stored Procedure that returns values from 3 simple tables as follow and need it to return a row for the product in all cases. I want to know if is it possible to get the value of BrandID in (T3) and if doesn't have a row for the product record returns a value of 0 for "BrandID"

T3 is like

ProductID --- BrandID

@iCategoryID int
AS
Begin
SELECT P.ProductID,P.CategoryID, C.ParentID, PC.BrandID FROM T1 P
JOIN T2 C ON P.CategoryID = C.CategoryID
JOIN T3 PC ON P.ProductID=PC.ProductID
WHERE P.CategoryID=@iCategoryID 
ORDER by P.ProductID Asc
End
GO

Upvotes: 0

Views: 148

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You want left outer join:

SELECT P.ProductID,P.CategoryID, C.ParentID, PC.BrandID
FROM T1 P LEFT JOIN
     T2 C
     ON P.CategoryID = C.CategoryID  LEFT JOIN
     T3 PC
     ON P.ProductID = PC.ProductID
WHERE P.CategoryID = @iCategoryID
ORDER by P.ProductID Asc;

This will return NULL if there is no match for brand. If you want 0 instead:

SELECT P.ProductID,P.CategoryID, C.ParentID, coalesce(PC.BrandID, 0) as BrandID
FROM T1 P LEFT JOIN
     T2 C
     ON P.CategoryID = C.CategoryID  LEFT JOIN
     T3 PC
     ON P.ProductID = PC.ProductID
WHERE P.CategoryID = @iCategoryID
ORDER by P.ProductID Asc;

Upvotes: 1

Related Questions