As a typical .net developer I have used SQL table join operation frequently. SQL gave us very rich way of manipulating data and we felt the luxury of it. The real fun happened when we where dealing with SharePoint 2007. In SharePoint 2007 we didn’t have direct way to join list and filter data programmatically. Link list concept was there but we need to use SharePoint designer to do it. Basically CAML query didn’t support list join operation (SharePoint 2007). In SharePoint 2007 my desired way of doing list join operation was through LINQ . But we had some performance issues with it. At some point we used third party tool to do the join operation for us. E.g. Bamboo List Roll-up web part
But thanks to Microsoft in 2010 release of SharePoint, it supports CAML query list join operations with the help of SPQuery class.
Recently I came across a customer requirement where I need to bring three lists information to a single view and present it to user in SharePoint 2010.I knew SharePoint 2010 supports list join operations so I started researching on how we join SharePoint lists using CAML query.
In My example I have employee List and department list. Employee list has lookup column to department list.
In SharePoint 2010 Use CAML query and SPQuery class for list join operation.
|Query||Filtering , order by commands|
|Joins||Inner Join or Left join commands|
|ProjectedFields||Lookup list fields|
|ViewFields||Available fields in result SPListItem|