From those two lists:

-date (DateTime)
-maxBookings (Number)

-availableDate (Lookup to AVAILABLEDATES:Id)
-User (Person)

I need to get all dates that their bookings don't reach maxBookings. In SQL it would be something like this:

SELECT ad.date
FROM AvailableDates ad
WHERE ad.maxBookings > (SELECT COUNT(*) FROM Bookings b WHERE b.availableDate = ad.Id)

How can I accomplish the same result via CAML-Query?


You have to use a CAML join like

SPQuery query = new SPQuery();
query.Join = ...

Have a look on the following link: http://msdn.microsoft.com/en-us/library/office/ee539975(v=office.14).aspx

  • But how can I compare maxBookings with elements returned? I know I can do it programatically, but I'd prefer using only a query. Or, at least, find the most efficient solution possible. – Bargant Nov 27 '13 at 13:24

If you have populated your look up column, then for CAML Query you can do something like this.

                  <FieldRef Name="availableDate" />
                  <Value Type="Choice">
                         The value you want to specify

Using enumerator etc. you can get the count for your bookings list and then you can check against your other list.


Finally, I used LINQ to accomplish it. Here you can see an example.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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