Help on a Left Join

Jul 20, 2015 at 2:30 PM
Edited Jul 20, 2015 at 2:31 PM
I could sure use some help developing a LeftJoin.

I have two lists. The "parent list" is named LMPackage with a field "Title" but was renamed to "PackageName". The "child list" is named LMPackageModification with a lookup field "LMPackage". This "LMPackage" field is the lookup field to the parent list. There is a one to one relationship between the lists.

Here is the query:

var lookupfield = "LMPackage";
var lookuplist = "LMPackageModification";
var query = new CamlBuilder()
.View(["Title","LMPackage"])
.LeftJoin(lookupfield,lookuplist)
.Select("LMPackage","LMPackage")
.RowLimit(10)
.ToString();
Coordinator
Jul 21, 2015 at 2:27 PM
Hi,

If I understood your explanations correctly, you are probably querying incorrect list.
I.e. if we use your terminology, you're querying "parent" while you should be querying "child".

When working with joins, you should always query the list that has the lookup in it.

SharePoint CAML queries are unfortunately quite far away from SQL in that sense.

I guess what you're trying to achieve is to show all modifications of every package. If that is correct, this can be achieved by querying LMPackageModification with .GroupBy("LMPackage").

And if you're trying to show PackageName for each modification, then you should use something like this (and again, query LMPackageModification list, not the LMPackage!):
var query = new CamlBuilder()
.View(["PackageName"])
.LeftJoin("LMPackage","LMPackage")
.Select("Title","PackageName")
.ToString();
P.S. Please keep in mind that if you're using MultiLookup field, joins are not possible. Discussed here.