InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 13:15:56 GMT20http://www.sqlservercentral.com/Forums/Topic823015-1683-1.aspxHiLet's consider next case:Tables: Sales 1&lt;--&gt; m Products 1&lt;--&gt;1 Country[code="sql"]Select *FROM Sales s inner join Products p on s.ProductId = p.ProductId inner join Country c on p.MadeInCountryId = c.CountryIdWHERE CountryName like 'A%'[/code]OR [code="sql"]Select *FROM Sales s inner join Products p inner join Country c on p.MadeInCountryId = c.CountryId on s.ProductId = p.ProductIdWHERE CountryName like 'A%'[/code]Mon, 07 Dec 2009 13:39:52 GMThalford13http://www.sqlservercentral.com/Forums/Topic823015-1683-1.aspxHi,Sorry, I should have usedSales s INNER JOIN Products p on c.ProductId = p.ProductIdnot CustomerId ... Thanks,LuigiMon, 07 Dec 2009 13:32:09 GMThalford13http://www.sqlservercentral.com/Forums/Topic823015-1683-1.aspxWhy not pass in XML into the SP? I.e.@BrandId_List XML --e.g. &lt;Brands&gt;&lt;id&gt;1&lt;/id&gt;&lt;id&gt;2&lt;/id&gt;&lt;/Brands&gt;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 NULLBy 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.Mon, 07 Dec 2009 09:38:27 GMTJoukahainenhttp://www.sqlservercentral.com/Forums/Topic823015-1683-1.aspxNot 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.Mon, 07 Dec 2009 09:22:08 GMTRob-506957http://www.sqlservercentral.com/Forums/Topic823015-1683-1.aspxPersonally, 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.Mon, 07 Dec 2009 08:59:44 GMTTim Overlundhttp://www.sqlservercentral.com/Forums/Topic823015-1683-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/JOIN/68746/">JOIN technique</A>[/B]Sun, 22 Nov 2009 14:26:11 GMThalford13