Merging and querying multiple lists

Question

I am still new to sharepoint and would like to know if it is possible to make a query that works across serveral lists. My list looks like this

Customers (id, name and so forth) Orders ( id, order number, customer and some additional info) OrderItems (id, name, price, description and so on)

I would like to create a view that will display the OrderItems grouped by Order which again will be grouped by Customer.

In pure .net code that is pretty easy but is it possible to implement it only using sharepoint lists?

Answer 1

Without custom code or third party components you would have only a few options. Using SharePoint Designer to create a Data View or creating a custom Query with some complex CAML which I'm not even sure is entirely possible.

Personally I would look more towards using Master Detail functionality using a combination of web part connections and filtering. By activating Enterprise features you have available a number of Filter Web Parts that should be able to be combined to filter lists to selected values.

Personally I have gone with custom code to bring back list data based on queries and then used the GetDataTable() method of the SPListItemCollection object. Once you have the list items in DataTables you have numerouse ways to sort filter and aggregate the information.

I should add to this that there is a great article on displaying information from a dataset using the SPGridView and SPMenuField. Once you have your DataTables you could establish relationships in a dataset to display the information using these controls:

http://blogs.msdn.com/powlo/archive/2007/02/25/displaying-custom-data-through-sharepoint-lists-using-spgridview-and-spmenufield.aspx

Answer 2

Connected web parts can do this...

Answer 3

I think SPD can do that. If you do not want get yourself dirty, take a look at SharePoint List Collection, which is perfect to you.