SQL Join

• shez104

  SSC Enthusiast

  Points: 193

  More actions

  January 4, 2004 at 4:15 am

  #85209

  I have 3 tables.

  TravelerLot

  WorkOrder

  Process

  My code is currently like this;

  SELECT WorkOrderLot.LotID, TravelerLot.TravLotID, Process.ProcNum

  FROM TravelerLot INNER JOIN

  WorkOrderLot ON TravelerLot.LotID = WorkOrderLot.LotID INNER JOIN

  Process ON TravelerLot.CurrentProcID = Process.ProcessID

  and it returns the following;

  WorkOrderLot.LotID TravelerLot.TravLotID Process.ProcNum

  10 18 24

  10 25 100

  10 9 13

  10 33 256

  However, I'm trying to return one record for each WorkOrderLot.LotID based off of the lowest Process.ProcNum value in the group. So using the above example I would want my view to return the following record;

  WorkOrderLot.LotID TravelerLot.TravLotID Process.ProcNum

  10 9 13

  WorkOrderLot is the parent to TravelerLot.

  Process is also a parent to TravelerLot.

  WorkOrderLot is one to many to TravelerLot.

  Process is one to many to TravelerLot.

• Gabor Nyul

  SSCrazy Eights

  Points: 9459

  More actions

  January 4, 2004 at 5:49 am

  #488573

  Here is one of the solutions:

  selectWorkOrderLot.LotID,

  TravelerLot.TravLotID,

  Process.ProcNum

  FROMTravelerLot INNER JOIN WorkOrderLot ON TravelerLot.LotID = WorkOrderLot.LotID

  JOIN Process ON TravelerLot.CurrentProcID = Process.ProcessID

  JOIN (selectLotID,

  min(ProcNum) as ProcNum

  fromProcess JOIN TravelerLot ON TravelerLot.CurrentProcID = Process.ProcessID

  group

  byLotID) as MinProcs ON TravelerLot.LotID = MinProcs.LotID AND MinProcs.ProcNum = Process.ProcNum

  Bye

  Gabor

  
  
  Bye
  Gabor