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?

3 Answers 3


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
    Commented Nov 27, 2013 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 and acknowledge you have read our privacy policy.

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