there are 3 List on My Scenario :
1) Tasks : Columns like this :
(Column Name) Title
--------------------------
TitleTest
TitleTest2
2) List1 : Column like :
(Column Name)Title test(Discription Type) tasklook(Lookup TasksList)
-----------------------------------------------
test1 blah blah TitleTest
test2 lorem ispum TitleTest2
3) List 2 : Column like :
(Column Name)Title reflist1(Lookup frm List1)
-----------------------------------------------
test1 test1
test2 test2
Camlquery with Join:
SPList olistbug = _web.Lists["List2"];
SPList first = _web.Lists["List1"];
SPQuery query1 = new SPQuery();
query1.Query = @"<OrderBy><FieldRef Name='Title' Ascending='True' /></OrderBy>";
// working query when use test column from list1.
query1.ProjectedFields = @"<Field Name='reftest' Type='Lookup' List='List1' ShowField='test' />";
query1.Joins = @"<Join Type='LEFT' ListAlias='List1'><Eq><FieldRef Name='reflist1' RefType='Id' /><FieldRef List='List1' Name='ID' /></Eq></Join>";
query1.ViewFields = @"<FieldRef Name='Title' /><FieldRef Name='reflist' /><FieldRef Name='reftest' />";
query1.RowLimit = 150000;
SPListItemCollection coll = olistbug.GetItems(query1);
but when use tasklook as a Its throwing Error.
query1.ProjectedFields = @"<Field Name='reftasklook' Type='Lookup' List='List1' ShowField='tasklook' />";
query1.Joins = @"<Join Type='LEFT' ListAlias='List1'><Eq><FieldRef Name='reflist1' RefType='Id' /><FieldRef List='List1' Name='ID' /></Eq></Join>";
query1.ViewFields = @"<FieldRef Name='Title' /><FieldRef Name='reflist' /><FieldRef Name='reftasklook' />";
