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).

Answer 2

The logic you are looking for, I believe, is this:

<Where>
   <Eq>
      <FieldRef Name='Analyst'/>
      <Value Type="Text"><FieldRef Name='Developer'/></Value>
   </Eq>
</Where>

I tested and this is not possible so the two options, as I see it, are:

1. Get all list items , then iterate through with JQuery with a find and compare for the two fields being equal.

2. Create a calculated column that sets a true or false value if the two columns are equal in the list and then do your select based on that column. This is probably the most expedient and also most effective from a performance perspective.

Answer 3

Try this:

<Where> 
  <Eq> 
    <FieldRef Name="Analyst" /> 
    <Value Type="Text">Developer</Value> 
  </Eq> 
</Where>

I have found a list that has two matching values to compare and I got a CAML query to work with the comparison; however, it is a boolean value so I am not sure if this is what you are looking for. It does compare two fields but I think the literal is still getting in the way. This list has about 25 entries in it and this is the only one that matched so I thought this would be a good test.

Here is the code:

private DataTable ExecuteQuery(SPList list) 
{ 
   SPQuery qry = new SPQuery(); 
 qry.Query = "<Where><And><Contains><FieldRef Name='Show' /><Value       Type='Boolean'>1</Value></Contains><Contains><FieldRef Name='Highlight' /><Value     Type='Boolean'>1</Value></Contains></And></Where>"; 
qry.ViewFields = "<FieldRef Name='Show' /><FieldRef Name='Highlight' />"; 
qry.IncludeMandatoryColumns = true; 
return list.GetItems(qry).GetDataTable();

Sorry if this is not what you are looking for. Good Luck!!