Technical Article

JOIN technique

,

I have next tables: Sales, Products, Brands
I want to display all sales for products belonging to a list of brands (Ids) ' ',2,3,5,6,' OR ALL Brands (if this list is empty)

 

Execution sample
exec dbo.sp_JoinTechnique ',2,3,5,6,'

 

So, I've tested on my data and works better than the regular way (maybe because of the indexes) but I want to know your opinions too.

 

Enjoy!

 

Create Procedure dbo.sp_JoinTechnique

@BrandId_List varchar = ''-- for all brands the list would be empty

AS 
BEGIN
/* Execution sample
exec dbo.sp_JoinTechnique ',2,3,5,6,' 

*/-- Using the function developed by my friend, CVMichael, and me,  the dbo.fn_SplitStringToTable
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 JOINProducts p on c.customerId = p.customerId
INNER JOINBrands 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 JOINProducts p on c.customerId = p.customerId
LEFT JOINBrands 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 JOINProducts 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
*/END

Rate

1.43 (7)

You rated this post out of 5. Change rating

Share

Share

Rate

1.43 (7)

You rated this post out of 5. Change rating