I've three lists: Competitions, Works and WorksVotes, linked to each other through Lookup fields. WorksVotes list has Lookup field to Works list, and Works list has Lookup field to Competitions list. What I need is to display the Competition in the WorksVotes list. I wrote some CAML code with 2 Joins which works fine with SPQuery object:
var query = new SPQuery();
query.Query = "<OrderBy><FieldRef Name=\"ID\" /></OrderBy>";
query.ViewFields = "<FieldRef Name=\"Title\" /><FieldRef Name=\"Attachments\" /><FieldRedf Name=\"LinkTitle\" /><FieldRef Name=\"CompetitionTitle\" />";
query.Joins =
@"<Join Type=""INNER"" ListAlias=""worksList"">
<Eq>
<FieldRef Name=""CompetitionWork"" RefType=""ID"" />
<FieldRef Name=""ID"" List=""worksList"" />
</Eq>
</Join>
<Join Type=""INNER"" ListAlias=""competitionsList"">
<Eq>
<FieldRef List=""worksList"" Name=""Competition"" RefType=""ID"" />
<FieldRef List=""competitionsList"" Name=""ID"" />
</Eq>
</Join>";
query.ProjectedFields = "<Field Name=\"CompetitionTitle\" Type=\"Lookup\" List=\"competitionsList\" ShowField=\"Title\" />";
After testing I added this CAML into schema.xml file that defines the WorksVotes list:
<ViewFields>
<FieldRef Name="Title"></FieldRef>
...
<FieldRef Name="CompetitionTitle"></FieldRef>
</ViewFields>
<Joins>
<Join Type="INNER" ListAlias="worksList">
<Eq>
<FieldRef Name="CompetitionWork" RefType="ID" />
<FieldRef Name="ID" List="worksList" />
</Eq>
</Join>
<Join Type="INNER" ListAlias="competitionsList">
<Eq>
<FieldRef List="worksList" Name="Competition" RefType="ID" />
<FieldRef List="competitionsList" Name="ID" />
</Eq>
</Join>
</Joins>
<ProjectedFields>
<Field Name="CompetitionTitle" Type="Lookup" List="competitionsList" ShowField="Title" />
</ProjectedFields>
But in this case I get an SPException telling that the "CompetitionTitle" field is unavailable or does not exist. What I'm doing wrong?