How to work CAML Joins like SQL Joins?

Question

I've checked articles on here and Googled till I'm blue in the finger tips. I've read and read and read and just can't seem to wrap my head around CAML Joins in Sharepoint2010.

Question: Can someone please show me a full example of how exactly CAML Join Query compares to 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's 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, my question is, can I get an exact example of this same operation is inacted upon in Sharepoint 2010 using the CAML Joins Query string?

Answer 1

Create your query from one of your lists.

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

To do the join, 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 choose the fields to display set query.ViewFields to

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

Then

SPListItemCollection result = tablea.GetItems(query);

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

Answer 2

My frustration mirrors yours, here's some more tips:

1. Start the query based on the child table in the relationship. (I cannot discern from your example which would be the parent and which the child.)

2. I agree with Rob Windsor that it must be based on a Lookup fields, but from my testing, it must be a lookup to a ListItemID type field. In SharePoint, this is the internal ID field. (I know this, because I have a lookup to a text field, and it just doesn't work. Hours of life wasted.) I currently have a post on the Microsoft Forums asking if the RefType parameter can be anything other than 'ID', so maybe keep an eye on that. Finally, if in the projected fields, the Type parameter must always be 'lookup', then why is it needed?

3. None of the CAML Query Builders(YACQB and U2U) support joins, so don't bother downloading and trying.