You can use the SPQuery.Join property in CAML to querying SharePoint lists data.
The each join is represented by a join element (which is child of Joins element). You can use joins to join multiple lists or use multiple joins to the same list.
Example:
I am going to show you how to use left outer join in CAML query.
The “EmployeeName” is a lookup field on a “Users” list. It looks up to the “ID” field of an “Employees” list. The “Employee” list in turn, has a “CountryName” field that is a lookup field to a “Countries List”. The first Join element assigns ‘employees’ as an alias for the “Employees” list. The second Join element assigns ‘employeeCountries’ as an alias to the “Countries” list. This query will define the join in parallel to the existing lookup relation between the “Employees” and “Countries” lists.
CAML Code:
<Joins>
<Join Type=’LEFT’ ListAlias= ‘employees’ >
<Eq>
<FieldRef Name=’EmployeeName’ RefType=’Id’ />
<FieldRef List=’employees’ Name=’ID’ />
</Eq>
</Join>
<Join Type=’LEFT’ ListAlias= ‘employeeCountries’ >
<Eq>
<FieldRef List=’employees’ Name= ‘CountryName’ RefType=’Id’ />
<FieldRef List=’ employeeCountries’ Name=’ID’ />
</Eq>
</Join>
</Joins>
