When lookup fields are created, sharepoint stores them as
ID;#Value format in related list.
for e.g.
in 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.
When having CAML query in List2
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 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 base on ID not by Value.
This can be achieved thru :
<Query>
<Where>
<Eq>
<FieldRef Name=”RefCountry” LookupId=”TRUE” />
<FieldRef Name=”RefCountry” LookupId=”TRUE” />
<Value Type=”Lookup”>10</Value>
</Eq>
</Where>
</Query>
where 10 is the ID for item having country=”India”
Come From: http://abstractspaces.wordpress.com/2008/05/05/caml-query-lookup-field-by-id-not-by-value/