CAML joins revisited

Using the following list

Example:

CAML LEFT JOIN

Trying to do a left join with CAML and found out that the only way to do it is if the lists are join with the ID field.

My Lists are set up this way,




Data for ActionList



Data for SubjectList


Here is the code that returns the data table for two columns from those two list.

        private DataTable getActionSubjectListData()
        {
            string[] fields = { "Title", "SubjectName" };
            foreach (string s in fields)
            {
                BoundField bf = new BoundField();
                bf.DataField = s;
                bf.HeaderText = s;
                spgActionSubjectList.Columns.Add(bf);
            }

            DataTable dt = new DataTable();

            dt.Columns.Add(new DataColumn("Title", Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("SubjectName", Type.GetType("System.String"))); 

            SPSite oSite = SPContext.Current.Site;
            SPWeb oWeb = SPContext.Current.Web;
            
            SPList actionList = oWeb.Lists["ActionList"];
            SPList subjectList = oWeb.Lists["SubjectList"];
            SPField actionSubjectID = actionList.Fields["SubjectID"];
            SPField subjectTitle = subjectList.Fields["Title"];
            SPField subjectIDD = subjectList.Fields["SubjectID"];
            SPQuery query = new SPQuery();

            query.Joins = "<Join Type='LEFT' ListAlias='SubjectList'>" +
                    "<Eq>" +
                    "<FieldRef Name='" + actionSubjectID.InternalName + "' RefType='Id'/>" +
                    "<FieldRef List='SubjectList' Name='ID'/>" +
                    "</Eq>" +
                    "</Join>";

            query.ProjectedFields = "<Field Name='SubjectName' Type='Lookup' List='SubjectList' ShowField='" + subjectTitle.InternalName + "'/> ";
            

            query.ViewFields = "<FieldRef Name='SubjectName' />" +
                                "<FieldRef Name='Title' />" ;

            query.Query = ""; 

            SPListItemCollection items = actionList.GetItems(query);

            foreach(SPListItem sli in items)
            {
                SPFieldLookupValue lkpSubjectName = new SPFieldLookupValue(sli["SubjectName"].ToString());
                 
                    dt.Rows.Add(sli.Title, lkpSubjectName.LookupValue);
            }
            return dt;
        }

Web Part

Caml Query Client Object

I was wondering why the list query returns ALL of my list when the reason was that I have left out the <Query> tag! This is the working version...

                SP.List oList = context.Web.Lists.GetByTitle("Projects");
                CamlQuery camlQuery = new CamlQuery();
                camlQuery.ViewXml = "" + projectCode + "";
                ListItemCollection collListItem = oList.GetItems(camlQuery);
                context.Load(collListItem);
                context.ExecuteQuery();