Using CAML Query to Retreive from Multiple Lists

Question

I am creating an applicant tool which contains the following lists:

Applicant; Applicant Answers; Job Questionnaire; Job Title

The lists are related as follows:

Applicant.ID = Applicant Answers.ApplicantID;
Applicant.JobApplicant = JobTitle.Title;
JobQuestionnaire.JobTitle = JobTitle.Title;
JobQuestionnaire.ID = Applicant Answers.QuestionID;

I need to load the questions and answers. I can load the answers from the Applicant Answer list, however, I cannot load the question text from there. Instead, I need to use the relationships listed above to retrieve the Question Text, which is a field in Job Questionnaire based on the Question ID.

I have done some research and noticed that I can't use one query to retrieve from two lists. What can I do in order to retrieve the Question Text?

Answer 1

You can query based on relations in two lists, please have a look at List Joins and Projections

You can use joins to get the projected field Question Text, something like:

SPQuery query = new SPQuery();

query.Join = @"
<Joins>
  <Join Type='INNER' ListAlias='Applicant Answers'>
    <Eq>
      <FieldRef Name='QuestionID' RefType='Id'/>
      <FieldRef List='JobQuestionnaire' Name='ID'/>
    </Eq>
  </Join>
</Joins>";

query.ProjectedFields = @"
<Field Name='ProjectQuestionText' Type='Text' List='JobQuestionnaire' ShowField='QuestionText'/>";

SPQuery.ViewFields = "<FieldRef Name='Title'/><FieldRef Name='ProjectQuestionText'/>";

SPList questionList = web.Lists["JobQuestionnaire"];
SPListItemCollection items = questionList.GetItems(query);                
foreach (SPListItem item in items)
{
    var questionText = item["ProjectQuestionText"].ToString();
}

Answer 2

You can use the same CAML query with the SPSiteDataQuery. You could query on list type or supply guids to identify each list using the SPSiteDataQuery.Lists property

how to query multiple lists using CAML

query.Lists = "<Lists><List ID='7A9FDBE6-0841-430a-8D9A-53355801B5D5' /><List ID='3D18F506-FCA1-451e-B645-2D720DC84FD8' /></Lists>";

More info http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spsitedataquery.lists.aspx