How to join two sharepoint list data using CAML or LINQ?

Question

I have two lists:

1. Doctors
2. DoctorDetails

In list #2 I have a lookup from list #1 i.e. on Doctor Name.

Now I want to fetch data on the basis of doctor name as filter from both lists.

How can I achieve this in SSOM as I'm working on a visual webpart?

Answer 1

Try this ,

  SPList customerList = Oweb.Lists["MarksMaster"];
        query.Query = @"<Query><Where><Neq><FieldRef Name='ID' /><Value Type='Counter'>null</Value></Neq></Where></Query>";

        query.Joins = @"<Join Type='Left' ListAlias='StudentMaster'>
                            <Eq>
                            <FieldRef Name='StudentID' RefType='ID'/>
                            <FieldRef List='MarksMaster' Name='ID'/>
                        </Eq>
                        </Join>";

        query.ProjectedFields = @"<field Name='StudentName' Type='Lookup' List='MarksMaster' ShowField='Title'/>
                                <field Name='StudentSection' Type='Lookup' List='MarksMaster' ShowField='Section'/> ";

        query.ViewFields = @"<FieldRef Name='StudentSection'/>
                            <FieldRef Name='ScienceGroup'/>";

Change the projectedFields as below,

   query.ProjectedFields =  @"<field Name='StudentName' Type='Lookup' List='StudentMaster' ShowField='Title'/>
                                <field Name='StudentSection' Type='Lookup' List='StudentMaster' ShowField='Section'/> ";

And Try this link also, How to join two sharepoint list in c# by CAML query?