0

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

2 Answers 2

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
            }; 
0
1

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!

2
  • Arsalan! I would appreciate complete CAML query. thanks for your help! Mar 30, 2013 at 2:17
  • I guess Ryan has already given the answer! Mar 30, 2013 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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