Results 1 to 2 of 2

Thread: SQL Server - JOIN technique

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2008
    Posts
    9

    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!

  2. #2
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    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
  •  



Click Here to Expand Forum to Full Width