I have two tables with a 1-M connection and have not been able to correctly convert the following SQL into a caml/spQuery I want every value in table A once no matter what & table B divided into two groups so I can count each group.

SELECT ID,Title,Count(Fav) as Favorite,Count(nfav) as Dislike FROM TableA JOIN (SELECT * FROM TableB WHERE boolValField = 1) Fav on Fav.GroupId = TableA.ID  JOIN (SELECT * FROM TableB WHERE boolValField = 0) nfav on nfav.GroupId = TableA.ID

I have been trying to follow http://msdn.microsoft.com/en-us/library/ee539975.aspx to join the tables->project viewfields->get projected columns but with no success

1 Answer 1


With your requirement I dont think depending on CAML query entirely is a very good choice. CAML Query does not support count() directly, so what i suggest is join tableA and tableB on tableB.GroupId = TableA.ID and fetch entire data, of course you can trim down the number of columns returned, that will make the query faster, convert splistitems to datatable and perform the Favorite/Dislike counting with LINQ to DataTable, that will be easier. You could also fetch 2 lists as datatable and perform everything including joining with LINQ but the amount of data you have that will not be a wise choice, alternatively dont use CAML at all, try to achieve this using LINQ to SharePoint


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.