List Join in SharePoint 2010 using CAML

1. Create a list Department and enter some values in it.

 
2. Create a list "Employee". Here create a column department and make it as look up column of Department -> Title. Enter some values in it.

 
3. Create a web part using visual studio and deploy.
   a) Drag and drop a grid view from tool box in the designing part.
   b) Write the below code to join the list.

       Code:
       using (SPSite site = new SPSite("http://win-j9h3l62i2n9:1000/"))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                        SPList list = web.Lists["Employee"];

                        SPQuery query = new SPQuery();
                        query.Query = @"<Query><Where><Eq><FieldRef Name='Gender' /><Value Type='Choice'>Male</Value></Eq></Where></Query>";

                        query.Joins = @"<Join Type='INNER' ListAlias='Department'>
                                            <Eq>
                                               <FieldRef Name='Department' RefType='ID' />
                                               <FieldRef List='Department' Name='ID' />
                                            </Eq>
                                        </Join>";

                        query.ProjectedFields =
                             @"<Field Name='Dep_Code' Type='Lookup' List='Department' ShowField='Title' />
                               <Field Name='Dep_Name' Type='Lookup' List='Department' ShowField='Name' />
                               <Field Name='Dep_Comment' Type='Lookup' List='Department' ShowField='Comment' />";

                        query.ViewFields = @"<FieldRef Name='Gender' />
                                             <FieldRef Name='Dep_Code' />
                                             <FieldRef Name='Dep_Name' />
                                             <FieldRef Name='Dep_Comment' />";

                        query.RowLimit = 100;

                        SPListItemCollection items = list.GetItems(query);

                        DataTable dt = items.GetDataTable();

                        grdShow.DataSource = dt;
                        grdShow.DataBind();
                    }
                }
 
4. Build and deploy.