Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

JOIN technique Expand / Collapse
Author
Message
Posted Sunday, November 22, 2009 2:26 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 4:06 PM
Points: 129, Visits: 141
Comments posted to this topic are about the item JOIN technique
Post #823015
Posted Monday, December 7, 2009 8:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, February 15, 2014 12:21 AM
Points: 111, Visits: 40
Personally, I would use dynamic SQL instead for best performance since the impact of indexes would be quite different depending on the Brand_IDs passed in.

I also believe that you need to add a FORCE ORDER query hint in order to make sure that the query optimizer respects the difference in join order between your two options.
Post #829959
Posted Monday, December 7, 2009 9:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 3, 2014 6:40 AM
Points: 11, Visits: 46
Not sure why you'd want your Products table to have a customerId column. If you have to track which customers have which products, I'd think that would be in a table other than Products.
Post #829991
Posted Monday, December 7, 2009 9:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 11, 2011 8:20 AM
Points: 2, Visits: 22
Why not pass in XML into the SP? I.e.

@BrandId_List XML --e.g. <Brands><id>1</id><id>2</id></Brands>

And then your query could look like this:

SELECT *
FROM
Sales s
INNER JOIN Products p on s.customerId = p.customerId
INNER JOIN Brands b on p.BrandId = b.BrandId
WHERE
tb.BrandId IN (SELECT Brands.ID.value('.','INT')
FROM @BrandId_List.nodes('/Brands/id') AS Brands(ID))
OR @BrandID_List IS NULL

By passing NULL for the parameter would still achieve the same result; you would just have to build the XML before passing into the SP. Also worth noting that you can apply an XML namespace here to make sure your XML markup conforms to a set standard.



Post #830015
Posted Monday, December 7, 2009 1:32 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 4:06 PM
Points: 129, Visits: 141
Hi,

Sorry, I should have used

Sales s
INNER JOIN Products p on c.ProductId = p.ProductId

not CustomerId ...

Thanks,
Luigi
Post #830240
Posted Monday, December 7, 2009 1:39 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 4:06 PM
Points: 129, Visits: 141
Hi

Let's consider next case:

Tables: Sales 1<--> m Products 1<-->1 Country
Select *
FROM
Sales s
inner join Products p on s.ProductId = p.ProductId
inner join Country c on p.MadeInCountryId = c.CountryId
WHERE CountryName like 'A%'

OR
Select *
FROM
Sales s
inner join Products p
inner join Country c on p.MadeInCountryId = c.CountryId
on s.ProductId = p.ProductId
WHERE CountryName like 'A%'

Post #830248
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse