CAML——Join多表连接查询

SPList list = web.Lists["Orders"];                     

SPQuery query = new SPQuery();

query.Joins = @"

  <Join Type=’LEFT’ ListAlias=’customers’>
    <Eq>
      <FieldRef Name=’CustomerName’ RefType=’Id’ />
      <FieldRef List=’customers’ Name=’ID’ />
    </Eq>
  </Join>

  <Join Type=’LEFT’ ListAlias=’customerCities’>
    <Eq>
      <FieldRef List=’customers’ Name=’CityName’ RefType=’Id’ />
      <FieldRef List=’customerCities’ Name=’ID’ />
    </Eq>
  </Join>
";

query.ProjectedFields = @"

  <Field 
    Name=’CustomerCity’

    Type=’Lookup’

    List=’customerCities’
    ShowField=’Title’ />
";

query.ViewFields = @"

<FieldRef Name='CustomerCity'/>

";

query.Query = @"


  <Where>

    <Eq>

      <FieldRef Name='CustomerCity'/>

      <Value Type='Text'>London</Value>

    </Eq>
  </Where>
";

 

SPListItemCollection items = list.GetItems(query);

reference：http://msdn.microsoft.com/zh-cn/library/ee539975.aspx