SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have 2 lists in SharePoint 2010. First List E lists all locations.

In the second list H I use first E list as lookup for Location. Every month users inputs information in list H.

List E has (Location)

List H has(Locations (lookup from list E), Month, Inventory, Name)

I need to know which location DID NOT provided inventory this month.

I used .net before and in SQL the statement I used was

SELECT    E.Canteen_Location
FROM    dbo.v_Location AS E LEFT OUTER JOIN
               dbo.tblInventory AS H ON H.Canteen = E.CanteenNum  And Month=@Month

Now we moved to SharePoint 2010 and I do not know if this is even possible. Can somebody help me with this?


share|improve this question
up vote 1 down vote accepted

Yes you can, in your Joins node add a Join with type="LEFT", something like this:

  <Join Type="LEFT" ListAlias="h">
      <FieldRef Name="Canteen" RefType="Id" />
      <FieldRef List="v_location" Name="CanteenNum" />

Here is a good example

You also may want to look into using LINQ to SharePoint with SPMetal as this will provide an easier syntax for querying SharePoint.

Syntax with LINQ would look like this:

var query = from e in db.v_location
            join h in db.tblInventory
               on h.Canteen equals e.CanteenNum into sh
            from x in sh.DefaultIfEmpty()
            where h.Canteen == null
            select new {
share|improve this answer
Thank you very much! – user2221018 Apr 5 '13 at 19:47


CAML Query do allow LEFT joins using <Join> tag... See Joins and Projections article on MSDN...

In Join there is a Type attribute whose value can either be INNER or LEFT, see JOIN...

If you can't figure out, I can give your complete CAML Query... Let me know!

share|improve this answer
Arsalan! I would appreciate complete CAML query. thanks for your help! – user2221018 Mar 30 '13 at 2:17
I guess Ryan has already given the answer! – Arsalan Adam Khatri Mar 30 '13 at 14:55

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.