Using CAML to query Sharepoint lists over Lookup fields

When lookup fields are created, SharePoint stores them as ID;#Value format in related list.

For example, in the list “Contacts” there is column called “Country”. There is item in contact with value “India” assigned in Country, having item ID=10.

Now for another list “List2” when column “Country” is stored as Lookup column named “refCountry”; and for certain data item “India” is selected from combo box, SharePoint stores it in,

10;#India in  “List 2” -> “refCountry” column

List Name:                          List1                                       List2

Column Name:                  Country                               refCountry à lookup for the “country” in list1

Column Value:                   India                                      10;#India

When we query List2 using CAML, The typical query looks like

<Query>

<Where>

<Eq>

<FieldRef Name=”RefCountry”  />

<Value Type=”Lookup”>India</Value>

</Eq>

</Where>

</Query>

The disadvantage of this way is, If the list Contacts is having more than one entry (item or row) having value India for Country it will return the only first one. This may give inconsistent data for further.

To avoid this query should be based on ID not by Value.

This can be achieved through:

<Query>

<Where>

<Eq>

<FieldRef Name=”RefCountry” LookupId=”TRUE” />

<Value Type=”Lookup”>10</Value>

</Eq>

</Where>

</Query>

where 10 is the ID for item having country=”India”