title: How to create a dataview In Sharepoint with data from a join query?
body: <p>I have 3 Lists in Sharepoint.</p>
  
  <p>I want to create a dataview that is a join of 3 tables.</p>
  
  <p>Table1 is joined with Table2 on FieldA
  Table 2 is joined to Table3 on FieldB</p>
  
  <p>Table1 has duplicate values in FieldA so I need to only return one value to join with Table2.</p>
  
  <p>In Access my query looks like this:
  SELECT DISTINCT WRK_InputWorkOrders.WorkOrder, Production1.[Part Number], Production1.[Work Order], Production1.Location, StationItems.Station, Production1.Description, Production1.Revision, WRK_InputWorkOrders.Status
  FROM StationItems INNER JOIN (WRK_InputWorkOrders INNER JOIN Production1 ON WRK_InputWorkOrders.WorkOrder = Production1.[Work Order]) ON StationItems.Item = Production1.[Part Number]
  WHERE (((WRK_InputWorkOrders.Status)&lt;>"closed"));</p>
  
  <p>Is there a way to write sql-like queries for dataviews?</p>
  
  <p>I have Sharepoint Designer 2007 and Access.</p>
  
  <p>The goal is to get a report that a user can view in Internet Explorer.
  I have tried using <a href="http://office.microsoft.com/en-us/sharepointdesigner/HA100991441033.aspx" rel="nofollow noreferrer">this</a> method. But it returns duplicate records
  I found <a href="http://blogs.msdn.com/timpash/archive/2005/11/23/XPATH-Filtering-Distinct-Values.aspx" rel="nofollow noreferrer">this</a> suggestion. It suggests using an XPath Filter
  not(@yourvalue = preceding-sibling::dfs:YourRepeatingRowName/@yourvalue)</p>
  
  <p>But wasn't able to get it to work. I don't know what to enter as YourRepeatingRowName</p>
  
  <p>I found <a href="http://blogs.msdn.com/sharepointdesigner/archive/2007/04/24/spdatasource-and-rollups-with-the-data-view.aspx" rel="nofollow noreferrer">this</a> link. Does anyone know if it can be used to perform such a join?</p>