Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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!!!

share|improve this question
add comment

3 Answers

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.

share|improve this answer
add comment

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
   }
}

EDITED:

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

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

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

END EDIT

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,

Andrew

share|improve this answer
    
Thanks Andrew. But What if there are multiple list and also not having lookup fields. In that case how can I retrieve data.? –  Rahul Gokani Mar 25 '13 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? –  Andrew Adamich Mar 25 '13 at 9:09
    
Hi, I have Edited my question. Please check that. –  Rahul Gokani Mar 25 '13 at 9:23
    
Just updated answer –  Andrew Adamich Mar 25 '13 at 10:22
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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