Limitations of the SharePoint join using CAML
- by ybbest
Limitation One
In SharePoint 2010, you can join the primary list to a foreign list and include more than one field from the foreign list. However, the limitation is that the included fields from foreign list have to be the following type:
Calculated (treated as plain text)
ContentTypeId
Counter
Currency
DateTime
Guid
Integer
Note (one-line only)
Number
Text
The above limitation also explains why you cannot include some types of the fields from the remote list when creating a lookup.
Limitation Two
When using CAML query to join SharePoint lists, there can be joins to multiple lists, multiple joins to the same list, and chains of joins. However, the limitations are only inner and left outer joins are permitted and the field in the primary list must be a Lookup type field that looks up to the field in the foreign list.
Limitation Three
The support for writing the JOIN query in CAML is very limited.I have to hand-code the CAML query to join the lists,not fun at all.Although some blogs post mentioned about using LINQ to SharePoint and get the CAML code from there , but I never get it to work.You can check this blog post for this.Let me know if it works for you.
References:
http://msdn.microsoft.com/en-us/library/ee535502.aspx
http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spquery.joins.aspx
