Left Outer Join in SharePoint 2010

Question

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
WHERE (H.Canteen IS NULL)

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

Larisa

Answer 1

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

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

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 {
               e.Canteen_Location
            }; 

Answer 2

Larisa,

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!