SQL Server - JOIN technique

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 :check:):

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!

You don't need to split, just convert your ID to string, then search that string in the list.

Like this:

Code:

---

DECLARE @BrandId_List VARCHAR(MAX)
SET @BrandId_List = ',123,2354,4567,'

SELECT *
FROM Sales s
        INNER JOIN        Products p on c.customerId = p.customerId
        INNER JOIN        Brands b on p.BrandId = b.BrandId
WHERE CHARINDEX(',' + CAST(b.brandId AS VARCHAR(10)) + ',', @BrandId_List) > 0
        OR NULLIF(@BrandId_List, '') IS NULL

---