Translating a SQL query to CAML query

Question

I am new to CAML query and as much as I have expected it to be simple like SQL I have found it quite frustrating to deal with. I have a simple query SQL query I am trying to translate into CAML but do not receive the expected result.

SQL: Select column1, column2, column3, column4, column5 from Table1 where column1 = 'Bob' and column2 = 'Marley';

My CAML query is: <Query><Where><And><Eq><FieldRef Name=\'Column1\'/><Value Type=\'Choice\'>Bob</Value></Eq><Eq><FieldRef Name=\'Column2/><Value Type=\'Choice\'>Marley</Value></Eq></And><Eq><FieldRef Name=\'Column3\'/></Eq><Eq><FieldRef Name=\'Column4\'/></Eq><Eq><FieldRef Name=\'Column5\'/></Eq>

Any help is appreciated. Thanks.

Answer 1

Assuming column1 and column2 are choice columns, the query should be:

<Query>
   <Where>
      <And>
         <Eq>
            <FieldRef Name='column1' />
            <Value Type='Choice'>Bob</Value>
         </Eq>
         <Eq>
            <FieldRef Name='column2' />
            <Value Type='Choice'>Marley</Value>
         </Eq>
      </And>
   </Where>
</Query>

You should also take into consideration that CAML field names will be case sensitive and the display name may not be the actual column name (for example "my column" would be "my_x0200_column").

You may want to check out the CAML query builder that will let you connect to a site, build the query using a wizard, show you the resultant CAML, and even let you test it: http://www.u2u.be/Software

Answer 2

When building CAML query it is not possible to specify multiple conditions in logical And & Or operators because logical operators only takes 2 conditions.

Read more here: http://buli.waw.pl/caml-query-multiple-conditions-in-and-or-clause/

Answer 3

<Query><Where><And><Eq><FieldRef Name='column1' /><Value Type='Choice'>Bob</Value></Eq><Eq><FieldRef Name='column2' /><Value Type='Choice'>Marley</Value></Eq></And></Where></Query>

Thats It.

The fields you're selecting aren't specified in the Query itself, they're specified in the ViewFields property. Try a tool like this to help you in the future: http://spcamlqueryhelper.codeplex.com/

Answer 4

There are several errors in the code you pasted.

Are you trying to use SPQuery object to do the query? If that's the case, try this out:

 SPList myList = SPContext.Current.Web.TryGetList("MyListTitle");

 SPQuery query = new SPQuery();

 query.Query = @"
 <Where>
  <And>
   <Eq><FieldRef Name='Column1' /><Value Type='Choice'>Bob</Value></Eq>
   <Eq><FieldRef Name='Column2' /><Value Type='Choice'>Marley</Value></Eq>
  </And>
 </Where>";

 query.ViewFields = "<FieldRef Name='Column1' /><FieldRef Name='Column2' /><FieldRef Name='Column3' /><FieldRef Name='Column4' /><FieldRef Name='Column5'/>";

 SPListItemCollection items = myList.GetItems(query);

Answer 5

For JavaScript -

var query = new SP.CamlQuery();
query.set_viewXml("<Query><Where><And><Eq><FieldRef Name='column1' /><Value Type='Choice'>Bob</Value></Eq><Eq><FieldRef Name='column2' /><Value Type='Choice'>Marley</Value></Eq></And></Where></Query>");