none
Need values from the "next" row.... RRS feed

  • Question

  • I have a table looking like this:

     

     

    idNode   idOrder   nodeSeqNo   Street   StreetNo   nodeType

    1            1              1                 St1        1             1803

    2            1              2                 St2        3             1804

    3            1              3                 St4        99           1803 

    4            1              4                 Av1       5              1804

    5            2              1                 St6       6              1803

    6            2              2                Rue4     4               1804

    ...

    ...

     

    where nodeType = 1803 means pickup node and 1804 means destination node. They will always come in pairs

     

    My challenge is to make a LINQ query which will give me something like this:

     

     

    idOrder pickupNodeId   destNodeId  PickupStreet PickupStreetNo DeliverStreet DeliverStreetNo

    1            1                      2                 St1                   1                   St2               3

    1            3                      4                 St4                  99                 Av1                5

    2            5                      6                 St6                  6                  Rue4              4

     

     

     

    Thanks,

    Jon H

    Tuesday, August 26, 2008 2:19 PM

Answers

  • You need to do a self join (joining a table back onto itself.)  

     

    from p in db.Stops where p.nodeType == 1803

    from d in db.Stops where p.nodeType == 1804

    where d.idOrder == p.idOrder && d.nodeSeqNo == p.nodeSeqNo + 1

    select new {

          p.idOrder,

          pickupNodeId = p.idNode,

          destNodeId = d.idNode,

          PickupStreet = p.Street,

          PickupStreetNo = p.StreetNo,

          DeliverStreet = d.Street,

          DeliverStreetNo = d.StreetNo

          };

     

           

     

     

    Wednesday, August 27, 2008 4:26 PM
    Moderator

All replies

  • Your database design does not seem right: How does the app know that idNode 2 is the destination for idNode 1, and not for idNode 3? And the same for idNode 4 - it could be from idNode 1 or idNode 3

     

    I can see that the destination idNode is one more than the source in your example - but if the idNode is an IDENTITY key you cannot guarantee that the numbers will be sequential (e.g. two orders entered at the same time ?)

    Tuesday, August 26, 2008 5:16 PM
    Answerer
  • You need to do a self join (joining a table back onto itself.)  

     

    from p in db.Stops where p.nodeType == 1803

    from d in db.Stops where p.nodeType == 1804

    where d.idOrder == p.idOrder && d.nodeSeqNo == p.nodeSeqNo + 1

    select new {

          p.idOrder,

          pickupNodeId = p.idNode,

          destNodeId = d.idNode,

          PickupStreet = p.Street,

          PickupStreetNo = p.StreetNo,

          DeliverStreet = d.Street,

          DeliverStreetNo = d.StreetNo

          };

     

           

     

     

    Wednesday, August 27, 2008 4:26 PM
    Moderator