SharePoint - Join two lists in CAML query - Asked By Anandh Ramanujam on 13-Dec-10 06:09 AM

Hi,

How could we combine two lists (like left join in sql) in CAML query.

Thanks.
Vivek Jagga replied to Anandh Ramanujam on 13-Dec-10 06:16 AM
Hi Anandh,

Best practice is to use LINQ on place of the Joins in CMAL query. You can do this by CAML query as

By CAML

public static List<Song> GetSongsByName(string titleContainsText)

{

List<Song> songs = new List<Song>();

XmlDocument camlDocument = new XmlDocument();

camlDocument.LoadXml(

@"<Where>

<Contains>

<FieldRef Name='Title' />

<Value Type='Text'>[titleContainsText]</Value>

</Contains>

</Where>".Replace("[titleContainsText]", titleContainsText));

using (SPSite site = new SPSite(siteUrl))

{

SPWeb web = site.OpenWeb();

SPQuery query = new SPQuery();

query.Query = camlDocument.InnerXml;

query.Joins =

@"

<Join Type='LEFT' ListAlias='Artist'>

<Eq>

<FieldRef Name='Artist' RefType='Id'/>

<FieldRef List='Artist' Name='ID'/>

</Eq>

</Join>

";

query.ProjectedFields =

@"

<Field Name='Country' Type='Lookup' List='Artist' ShowField='Country'/>

";

SPListItemCollection items = web.Lists["Songs"].GetItems(query);

IEnumerable<Song> sortedItems =

from item in items.OfType<SPListItem>()

orderby item.Title

select new Song { SongName = item.Title, SongID = item.ID, Country =

item["Country"].ToString()};

songs.AddRange(sortedItems);

}

return songs;

}


By LINQ

private static void SimpleLINQQuery()

{

Trace("Simple LINQ Query", true);

using (SampledataDataContext context = new SampledataDataContext(siteUrl))

{

var artistsMichael = from artist in context.Artists

where artist.Title.Contains("Michael")

select artist;

foreach (var artist in artistsMichael)

{

Trace(artist.Title);

}

}

}

Anoop S replied to Anandh Ramanujam on 13-Dec-10 06:16 AM
Unfortunately CAML doesn't provide any JOIN mechanism. What you would have to do is to retrieve all data to DataTables and then perform the merge.
Vivek Jagga replied to Anoop S on 13-Dec-10 06:30 AM
Anoop,

SP2010 supports join in CAML query.