# VBForums CodeBank > CodeBank - Other >  SQL Server - JOIN technique

## halford13

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



```
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:



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



```
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!

----------


## CVMichael

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

Like this:


```
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
```

----------

