SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


JOIN technique


JOIN technique

Author
Message
halford13
halford13
SSC Veteran
SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)

Group: General Forum Members
Points: 298 Visits: 147
Comments posted to this topic are about the item JOIN technique
Tim Overlund
Tim Overlund
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 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.
Rob-506957
Rob-506957
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 63
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.
Joukahainen
Joukahainen
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 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.
halford13
halford13
SSC Veteran
SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)

Group: General Forum Members
Points: 298 Visits: 147
Hi,

Sorry, I should have used

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

not CustomerId ...

Thanks,
Luigi
halford13
halford13
SSC Veteran
SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)

Group: General Forum Members
Points: 298 Visits: 147
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%'


Iwas Bornready
Iwas Bornready
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32000 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search