How to create a dataview In Sharepoint with data from a join query?

Question

I have 3 Lists in Sharepoint.

I want to create a dataview that is a join of 3 tables.

Table1 is joined with Table2 on FieldA Table 2 is joined to Table3 on FieldB

Table1 has duplicate values in FieldA so I need to only return one value to join with Table2.

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)<>"closed"));

Is there a way to write sql-like queries for dataviews?

I have Sharepoint Designer 2007 and Access.

The goal is to get a report that a user can view in Internet Explorer. I have tried using this method. But it returns duplicate records I found this suggestion. It suggests using an XPath Filter not(@yourvalue = preceding-sibling::dfs:YourRepeatingRowName/@yourvalue)

But wasn't able to get it to work. I don't know what to enter as YourRepeatingRowName

I found this link. Does anyone know if it can be used to perform such a join?

Answer 1

Your question is more of an ADO.NET question. Unfortunately ADO.NET doesn't have an easy way to do this, which is why companies like bamboo Solutions builds theirCross List Web Part: http://store.bamboosolutions.com/pc-42-1-cross-list-web-part.aspx

Otherwise I would attempt to use LINQ to query the tables. You might have more luck doing that.

Here is an example of a JOIN query provided by MS (I only changed the first two DataTable lines to represent filling a DataTable with an SPListItemCollection object)

DataTable orders = spListCol1.ToDataTable();
DataTable details = spListCol2.ToDataTable();

var query =
    from order in orders.AsEnumerable()
    join detail in details.AsEnumerable()
    on order.Field<int>("SalesOrderID") equals
        detail.Field<int>("SalesOrderID")
    where order.Field<bool>("OnlineOrderFlag") == true
    && order.Field<DateTime>("OrderDate").Month == 8
    select new
    {
        SalesOrderID =
            order.Field<int>("SalesOrderID"),
        SalesOrderDetailID =
            detail.Field<int>("SalesOrderDetailID"),
        OrderDate =
            order.Field<DateTime>("OrderDate"),
        ProductID =
            detail.Field<int>("ProductID")
    };

DataTable orderTable = query.CopyToDataTable();