I have next tables: Sales, Products, Brands
I want to display all sales for products belonging to a list of brands (Ids) ',123,2354,4567,' OR ALL Brands (if this list is empty)
Using the function developed by CVMichael and me, dbo.fn_SplitStringToTable http://www.vbforums.com/showthread.php?t=590692
My stored procedure receives the list of brands as:
@BrandId_List = ',2,3,5,6,' -- for all brands the list would be empty
Code:
DECLARE @tbl_Brands TABLE (BrandID INT)
INSERT INTO @tbl_Brands
SELECT ItemData
FROM dbo.fn_SplitStringToTable (ISNULL(@BrandId_List,''),',') your_Brands
I can do it like this:
Code:
SELECT *
FROM
Sales s
INNER JOIN Products p on c.customerId = p.customerId
INNER JOIN Brands b on p.BrandId = b.BrandId -- do the LEFT JOIN Brands if you have products without brands
LEFT JOIN @tbl_Brands tb on b.brandId = tb.BrandId
WHERE
@BrandId_List = ''
OR
tb.BrandId is NOT NULL
--OR I can do it like this (the way I RECOMMEND ):
Code:
SELECT *
FROM
Sales s
INNER JOIN Products p on c.customerId = p.customerId
LEFT JOIN Brands b
INNER JOIN @tbl_Brands tb on b.brandId = tb.BrandId
on p.BrandId = b.BrandId
WHERE
@BrandId_List = ''
OR
b.BrandId is NOT NULL
/* You can use this technique instead of:
... INNER JOIN Products p on c.customerId = p.customerId
LEFT JOIN (SELECT br.* FROM Brands br INNER JOIN @tbl_Brands tb on br.brandId = tb.BrandId) b
on p.BrandId = b.BrandId
*/
Enjoy!