CAML Query multiple Lists with same fields

Question

I have 4 different lists with different fields.

Now I would like to retrieve items from all 4 of these lists ordered by user "Created By" ("Author") field using CAML.

Also, all of the lists have a common field "Cost" which I would like to retrieve from each item.

Is it possible to do this using just one CAML query?

Answer 1

Answer to main question is no. But You can use SPSiteDataQuery with CAML query to define search criteria, for example:

SPSiteDataQuery spQuery = new SPSiteDataQuery
{
    Lists = "<Lists ServerTemplate=\"101\" />",
    Query = Camlex.Query().Where(i => i["Title"] == "test value").ToString(),
    ViewFields = Camlex.Query().ViewFields(new List<string> {"ID"}),
    RowLimit = 100
};
var dataTable = web.GetSiteData(spQuery);

foreach (DataRow datarow in dataTable.Rows)
{
    var listGuid = new Guid(datarow["ListId"]+"");
    var itemId = int.Parse(datarow["ID"]+"");

    // process items
}

This article explains the usage of SPSiteDataQuery.