-
Nov 12th, 2009, 02:54 PM
#1
Thread Starter
New Member
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 ):
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!
-
Nov 12th, 2009, 03:22 PM
#2
Re: SQL Server - JOIN technique
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
Last edited by CVMichael; Nov 12th, 2009 at 03:37 PM.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|