JOIN technique

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Luigi Marinescu » JOIN technique

JOIN technique

Rate Topic

Display Mode

Topic Options

Author

Message

halford13

Posted Sunday, November 22, 2009 2:26 PM

SSC-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

Tim Overlund

Posted Monday, December 07, 2009 8:59 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 02, 2011 10:53 AM
Points: 111, Visits: 39

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

Rob-506957

Posted Monday, December 07, 2009 9:22 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, January 14, 2013 1:09 PM
Points: 11, Visits: 44

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

Joukahainen

Posted Monday, December 07, 2009 9:38 AM

Forum 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

halford13

Posted Monday, December 07, 2009 1:32 PM

SSC-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

halford13

Posted Monday, December 07, 2009 1:39 PM

SSC-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%'

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 »

Permissions