SPQuery - join two tables and count each

Question

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

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