I am new to SharePoint. I am developing a web-part in that I am trying to join two list.
I have a same web-part in that lists were join using LINQ query.
But it takes more time to fetch the data. So I am trying to convert it into CAML query.

I want to know that is there any way to achieve CAML.Join between two lists without any lookup columns.???

Or Is there any way to convert LINQ ResultSet into direct DataTable. I mean, to data from the LINQ query in DataTabel directly.???

List Department:

Title | Name 
SA    | Sales
HR    | HumanResource
FI    | Finance

List Employee:

Title | Name | Department
Mr    | ABC  | Sales
Mr    | XYZ  | Finance
Mr    | MNO  | Sales
Mr    | PQR  | HumanResource

In the above lists Employee list has the Department Column which is the lookup field from department. I want to get the Employee data when Department Column is not the LOOKUP Field And also using CAML Query.

Any suggestion will be appreciated.
Thank you in advance!!!

3 Answers 3


Generally When you are using CAML Query JOIN It is necessary to have a LookUp field in your list otherwise it will not allow to have Join Operation Between two list.

I have tried without lookup field but it not worked for me too.


There are requirements to keep in mind when creating list joins.

You cannot join just any two lists, regardless of type. And if two lists can be joined, not just any primary and foreign field can be used as the "join on" pair of fields. The field in the primary list must be a Lookup type field and it must lookup to the field in the foreign list. For this reason, all joins mirror existing lookup relations between lists.


Yes it is. There is a way to get DataTable directly from result returned by CAML query.

To do this you should use

// Source data retriever
SPSiteDataQuery query = new SPSiteDataQuery();
// Table to hold retrieved information
DataTable dt = null;

// Set data retrieve settigns
query.Lists = "<Lists BaseType='1'/>";
query.Webs = "<Webs Scope=\"Recursive\" />";
query.ViewFields = "<FieldRef Name=\"" + Fields.Title + "\" />" + "" +
"<FieldRef Name=\"Name\" />" + "" +
"<FieldRef Name=\"Fileref\" />" + "" +
"\" />";

query.Query = CamlQuery;
dt = web.GetSiteData(query);

if (dt != null)
   foreach (DataRow row in dt.Rows)
      // Do your job


To get Employes by its department you can use next CAML query by specifying right values:

            <FieldRef Name='ContentType' />
            <Value Type='Computed'>Employee</Value>
            <FieldRef Name='Custom_Department_Column_Static_Name' />
            <Value Type='Text'>SomeThing</Value>

This will search for list with "Employee" content type and select all where "Custom_Department_Column_Static_Name" column text equals "SomeThing". If you will need to get values from "Employee" list based on specific unknown values of "Department" list you will firstly need to get desired values from "Department" and then get "Employee" ibnfo based on previously selected values. Two CAML queries...


In data table fields specified in query.ViewFields created as column names. You should specify all fields in query.ViewFields if you use them.

web is a web site where lists are stored.

When get your list you just should find a way to get only your two lists (create where clause which will return only your two lists). In my case I have filtered this by content type. This was a custom content type and plus to this I had to take lots of information so it was good for me.

Hope it helps,


  • Thanks Andrew. But What if there are multiple list and also not having lookup fields. In that case how can I retrieve data.? Mar 25, 2013 at 4:14
  • Hi, you can get information from these lists. As told before if you can identify your lists (by content type, uniqueId, by some any other property) you can will get information from these lists. Lookup is not an issue or I just do not understand well your question. Can you provide definition of your lists so I can see and advice how to implement this? Mar 25, 2013 at 9:09
  • Hi, I have Edited my question. Please check that. Mar 25, 2013 at 9:23
  • Just updated answer Mar 25, 2013 at 10:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.