The "Right" Way to Join Two Lists In a DVWP

As long as I’ve been working with Data View Web Parts (DVWPs), I’ve pretty much always done my “joins” manually by diving into the XSL.  When I started working with DVWPs, there wasn’t much available to tell me how to do things, so I just made it up based on what worked.

Today I saw this video from Laura Rogers that showed the way you are probably “supposed” to do a “join” in your DVWP.  Take a look.  I may have once followed this route, but it would have been long ago!

A few observations on all of this:

• When you create a Linked Source, what actually happens is that the information about the sources is saved into an XML file in the _catalogs/fpdatasources Document Library in the current site.  What this XML file contains is information about the SharePoint:AggregateDataSource in a udc:DataSource wrapper.   Each time you use the Linked Source, this information is copied in as the SharePoint:AggregateDataSource for your DVWP.  Think of it as a SharePoint:AggregateDataSource template.  If you can make the CAML more efficient, and you expect to reuse the Linked Source, you can do it here.
• Upon initial creation of the DVWP from the Linked Source, there are templates in the DVWP named dvt_1, dvt_1.body, and dvt_1.rowview.  (Depending upon the other options you choose from there, you may end up with more dvt_1.* templates.)
• Note that both Data Sources (in this case; there can be many) have the selectcommand=”<View></View>” by default, so there is no efficiency in the “join”.  I haven’t seen issues with this, even with thousands of rows, but folks often [virtually] look at me askance when I suggest doing the filtering after the items have been retrieved.  (I often reply with “I learned to program in 16k of memory, so this makes me blanch, too, but I just haven’t seen performance problems.)  If you expect that you will have large numbers of items in your lists, think about how you can add WHERE clauses into your CAML to reduce the number of items retrieved initially as I mention above.
• When you add the first Joined Subview, what happens is that a second set of templates called dvt_2, dvt_2.body, and dvt_2.rowview are created in your DVWP. This set of templates is for the second Data Source (thus the names).
• The “join” isn’t really a join at all.  The dvt_1.rowview template calls the dvt_2 template and the values of the columns which you specify in the Joined Subview dialog are used to filter the second Data Source, something like this:

  <xsl:call-template name="dvt_2">       
  <xsl:variable name="dvt_ParentRow" select="current()" />         
  <xsl:variable name="Rows" select="../../../Assignees/Rows/Row&#91;@Title=$dvt_ParentRow/@Title&#93;" />

You can watch all of these things happen if you follow along with Laura’s video and do the same things that she is doing in Split mode in SharePoint Designer.  At this point, you’re sort of where I end up when I do most of this manually.  However, there are some best practices which I follow which I thought I’d pass along:

• Rename the templates from dvt_1.* and dvt_2.* to match your list names.  So, given the code above, I’d rename dvt_2 to Assignees, dvt_2.body to Assignees.body, and dvt_2.rowview to Assignees.rowview.  This makes following even your own code much easier, and it certainly ought to make more sense to someone else who gets stuck with it.
• Rather than using the dvt_ParentRow logic above, call the dvt_2 template (which is called something like Assignees now, right?) with the columns you want to “join” on as parameters, something like this:

  <xsl:call-template name="dvt_2">       
      <xsl:with-param name="Title" select="@Title"/>        
  </xsl:call-template>

  and then the top of your dvt_2 template looks like this:

  <xsl:template name="dvt_2">        
    <xsl:param name="Title""/>        
    <xsl:variable name="Rows" select="../../../Assignees/Rows/Row&#91;@Title=$Title&#93;" /> 

• This way, if you need to “join” (notice how I keep putting “join” in quotes) based on multiple columns or a calculation, you’re positioned for it.  Simply pass in the additional parameter(s) and add the logic to the filter in dvt_2.

Once you’re as familiar with DVWPs as I am, you may forego the dialogs, too, but hopefully this gives you some better ideas about what’s going on “under the hood”.