0

After much research, I can't seem to find a CAML equivalent of

SELECT i.Name, i.SubmitDate
FROM issues i 
WHERE i.ServerName IN (
    SELECT s.Name
    FROM servers s 
    WHERE s.Active = true
)

Or

SELECT i.Name, i.SubmitDate
FROM issues i 
INNER JOIN servers s ON s.Name = i.ServerName
WHERE s.Active = true

This is what I have so far. I've tried <Includes> and <In> but I can't seem to get the syntax. Or maybe I approaching this wrong. I can't use LINQ either as i need to pass the CAML query into the SharePoint client object model Any ideas?

<Query>

<Where>
<Includes>
<FieldRef Name="Name" />
<Value Type="Lookup" />
</Includes>
</Where>

<ViewFields>
<FieldRef Name="Name" />
<FieldRef Name="SubmitDate" />
</ViewFields>

</Query>
Eric
  • 505
  • 5
  • 22
  • The obvious one, two CAML queries, first you get all the values from the inner query and create another query with a big OR (more efficient that multiple independent queries). – SJuan76 Aug 12 '15 at 18:53
  • @SJuan76 the issue would be that I am still getting all of the issues back from the server, when if i pass in a CAML query, i only retrieve back the filtered data. – Eric Aug 12 '15 at 19:02

1 Answers1

1

I'm not sure why you object to the original query and I don't know CAML. But this query should be equivalent:

SELECT i.Name, i.SubmitDate
FROM issues i INNER JOIN servers s ON s.Name = ServerName
WHERE s.Active = true
shawnt00
  • 16,443
  • 3
  • 17
  • 22
  • Also I personally don't object to using IN but isn't there A bit of a performance hit when you get into larger record amounts compare to a Inner join – Eric Aug 12 '15 at 19:14
  • 1
    `IN` often results in an identical plan. And actually it's often faster because it's easy to short-circuit once you determine whether something is in the list. A regular join on the other hand requires it to build all the rows in the cross product. – shawnt00 Aug 13 '15 at 03:29