November 22, 2009 at 2:26 pm
Comments posted to this topic are about the item JOIN technique
December 7, 2009 at 8:59 am
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.
December 7, 2009 at 9:22 am
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.
December 7, 2009 at 9:38 am
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 JOINProducts p on s.customerId = p.customerId
INNER JOINBrands 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.
December 7, 2009 at 1:32 pm
Hi,
Sorry, I should have used
Sales s
INNER JOINProducts p on c.ProductId = p.ProductId
not CustomerId ...
Thanks,
Luigi
December 7, 2009 at 1:39 pm
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%'
May 23, 2016 at 7:07 am
Thanks for the script.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply