Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers

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">
    <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
            }; 
share|improve this answer
    
Thank you very much! –  user2221018 Apr 5 '13 at 19:47
add comment

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!

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
add comment

Your Answer

 
discard

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.