How to work with CAML connections, such as SQL connections?

Question

I checked the articles here and googled until I got the blue at my fingertips. I read, read and read, and it seems I can’t wrap my head around CAML Joins in Sharepoint2010.

Question: Can someone please show me a complete example of exactly how CAML Join Query compares with SQL Join Query?

*For Example (SQL of Course, just something to work with)* If I had a Database named "whatever" & it contained two tables. We'll name these "tableA" & "tableB", respectively. Let say they look like this: - tableA - ID | Column1 | Column2 | Column3 - tableB - ID | Column4 | Column5 | Column6 SELECT tableA.Column1, tableA.Column2, tableB.Column4 FROM tableA INNER JOIN tableB ON tableA.ID = tableB.ID Would give me something like: - newTable - Column1 | Column2 | Column4 result | result | result result | result | result result | result | result 

So, again, to my question: can I get an exact example of this same operation that uses Sharepoint 2010 using the CAML Joins Query line?

Answer 1

Create a query from one of your lists.

  SPList list = SPContext.Current.Site.RootWeb.Lists["TableA"]; SPQuery query = new SPQuery(); 

To complete the connection, set query.Joins to

  <Join Type="INNER" ListAlias="TableB"> <Eq> <FieldRef Name="TableA" RefType="ID" /> <FieldRef List="TableB" Name="ID" /> </Eq> </Join> 

and query.ProjectedFields to

  <Field Name="TableBColumn4" Type="Lookup" List="TableB" ShowField="Column4"> 

To select the fields to display the query.ViewFields set on

  <FieldRef Name="Column1"> <FieldRef Name="Column2"> <FieldRef Name="TableBColumn4"> 

Then

 SPListItemCollection result = tablea.GetItems(query); 

Or something like this (it's from memory!)

Answer 2

My disappointment reflects yours, here are some more tips:

• Run a query based on a child table in a relation. (I cannot distinguish your example, which would be a parent and which would be a child.)

• I agree with Rob Windsor that it should be based on Lookup fields, but from my testing it should be a search in a field of type ListItemID. In SharePoint, this is an internal identifier field. (I know this because I have a search in the text box and it just doesn’t work. The lifetime is gone.) I currently have a message on Microsoft forums asking if the RefType parameter can be anything other than ID ', so maybe keep an eye on this. Finally, if the Type parameter should always be “lookup” in the projected fields, then why is it needed?

• None of the CAML request collectors (YACQB and U2U) support connections, so don't worry about downloading and trying.