Using CAML to compare SharePoint "Person or Group" fields

Question

I have a SharePoint 2007 (MOSS) list with 2 "Person or Group" columns which I would like to compare, essentially:

SELECT * FROM List WHERE (Analyst = Developer)

In my code (C#) I've built this as:

SPQuery itemQuery = new SPQuery();
itemQuery.Query = "<Where><Eq><FieldRef Name='Analyst' /><FieldRef Name='Developer' /></Eq></Where>";

SPListItemCollection queryResults = list.GetItems(itemQuery);

But this is throwing an exception ("Exception from HRESULT: 0x80020009 (DISP_E_EXCEPTION)").

I've seen some information regarding the Type attribute of a Value element when comparing against a "Person or Group" column, but nothing about comparing two of these columns against each other.

I've also tried adding "LookupId='TRUE'" to each FieldRef, with no change.

Answer 1

It is not possible to compare two fields like that using CAML. You have to use a literal value. This means, that you'll likely have two queries:

1. Retrieve the Analyst/Developer's user ID
2. Retrieve items where Analyst and Developer are the same

Example of #2:

<Where>
   <And>
      <Eq><FieldRef Name="Analyst" LookupId="TRUE"/><Value Type="Integer">42</Value></Eq>
      <Eq><FieldRef Name="Developer" LookupId="TRUE"/><Value Type="Integer">42</Value></Eq>
   </And>
</Where>

FYI, you can also use <UserID/> for the "Current User" instead of user ID (in this example, 42).