0

Anyway been stuck for quite some time on this query.

    SELECT
    [cr].[ItemID]
   ,[cr].[LatestEnteredDate]
   ,[cr].[LatestSentDate]
   ,[cr].[LatestReceivedDate]
   ,CASE WHEN LatestSentDate IS NULL
              OR ISNULL([LatestReceivedDate],'1900-01-01') < LatestEnteredDate THEN 1
         ELSE 0
    END AS Outstanding

   ,j.[JobNo]
   ,j.[VersionRef]
   ,j.[CardTitle]
   ,i.[BarcodeNo]
   ,i.[SerialNo]
   ,i.[BundleNo]
   ,i.[CartonNo]
   ,i.[PalletNo]
FROM
    (
     SELECT
        [cri].[ItemID]
                   --,[cri].[EnteredBy]
       ,MAX([cri].[EnteredDate]) AS LatestEnteredDate
       ,MAX([crr].[SentDate]) AS LatestSentDate
       ,MAX([crx].[ReceivedDate]) AS LatestReceivedDate
     FROM
        [dbo].[CardReissue] AS cri
     LEFT JOIN [dbo].[CardReissueRequests] AS crr ON [cri].[ItemID] = [crr].[ItemID]
     LEFT JOIN [dbo].[CardReissueReceipts] AS crx ON [cri].[ItemID] = [crx].[ItemID]
     GROUP BY
        [cri].[ItemID]
    ) cr
INNER JOIN [dbo].[Items] AS i ON i.ID = cr.[ItemID]
INNER JOIN [dbo].[Jobs] AS j ON [i].[JobID] = [j].[ID]

Tried to break it down into steps, so decided to start with left join to two tables (T1 to T2 and T1 to T3)

SELECT
        [cri].[ItemID]
                   --,[cri].[EnteredBy]
       ,([cri].[EnteredDate]) 
       ,([crr].[SentDate]) 
       ,([crx].[ReceivedDate]) 
     FROM
        [dbo].[CardReissue] AS cri
     LEFT JOIN [dbo].[CardReissueRequests] AS crr ON [cri].[ItemID] = [crr].[ItemID]
     LEFT JOIN [dbo].[CardReissueReceipts] AS crx ON [cri].[ItemID] = [crx].[ItemID]

Got as far as this in LinqPd:

    var query =(
            from cr in CardReissues
            join crreq in CardReissueRequests
                on cr.ItemID equals crreq.ItemID into req
            join crrx in CardReissueReceipts
                on cr.ItemID equals crrx.ItemID
                orderby cr.EnteredDate
                from rx in req.DefaultIfEmpty()
            select new { 
            //cr.ItemID,
            rx.ItemID,
            cr.EnteredDate,
            rx.SentDate,
            crrx.ReceivedDate
        }
).ToList();

But this is giving me an inner join to the first table:

SELECT [t2].[ItemID] AS [ItemID], [t0].[EnteredDate], [t2].[SentDate] AS [SentDate], [t1].[ReceivedDate]
FROM [CardReissue] AS [t0]
INNER JOIN [CardReissueReceipts] AS [t1] ON [t0].[ItemID] = [t1].[ItemID]
LEFT OUTER JOIN [CardReissueRequests] AS [t2] ON [t0].[ItemID] = [t2].[ItemID]
ORDER BY [t0].[EnteredDate]

Could someone point me in the right direction?

Servy
  • 202,030
  • 26
  • 332
  • 449
mark1234
  • 1,110
  • 2
  • 24
  • 41
  • 1
    Here are two resources I find quite helpful for improving linq-foo... http://www.hookedonlinq.com/ and http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b – Brocco Apr 10 '14 at 20:05
  • You can just throw "left outer join in LINQ" into a search engine to see how to perform a left outer join in LINQ. – Servy Apr 10 '14 at 20:06
  • Try to create some extra Views and use them. It will save your time. – NoWar Apr 10 '14 at 20:07
  • Use navigation properties when and where possible. Navigation properties are implicit joins. – Gert Arnold Apr 10 '14 at 20:39
  • thanks for all replies. I had googled many examples, but couldn't get mine to work. I was trying to avoid using views (but for now will resort to one because lightswitch can only use data that is the same shape as an entity - ultimately will try and figure out RIA). I use navigation properties for most of my stuff, but this is an unusual situation (I can't use the PK in any of the 3 tables for the FK relationship and LS doesn't seem to handle relationships that do not use a PK in the referencing table of the FK) – mark1234 Apr 11 '14 at 15:32

0 Answers0