Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

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

share|improve this answer
    
I cannot use linq, thanks for the help! –  user13186 Nov 27 '12 at 2:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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