Having trouble creating code for a VIEW in MS SQL2000. Please help.

[Digitalcandy]

Your help would be much appreciated.

I have 3 tables.

dbo.TravelerLot
dbo.WorkOrder
dbo.Process

My code is currently like this;

SELECT dbo.WorkOrderLot.LotID, dbo.TravelerLot.TravLotID, dbo.Process.ProcNum
FROM dbo.TravelerLot INNER JOIN
dbo.WorkOrderLot ON dbo.TravelerLot.LotID = dbo.WorkOrderLot.LotID INNER JOIN
dbo.Process ON dbo.TravelerLot.CurrentProcID = dbo.Process.ProcessID

and it returns the following;

dbo.WorkOrderLot.LotID dbo.TravelerLot.TravLotID dbo.Process.ProcNum
10 18 24
10 25 100
10 9 13
10 33 256



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

dbo.WorkOrderLot.LotID dbo.TravelerLot.TravLotID dbo.Process.ProcNum
10 9 13






Thanks for your help. Formatting this post was a pain in the buttocks!

 

[edwinjs]

Some Sample data with structure of data would help

 

[Digitalcandy]

My original post is sample data. The joins I have listed are the only ones possible.

dbo.WorkOrderLot is the parent to dbo.TravelerLot.
dbo.Process is also a parent to dbo.TravelerLot.

dbo.WorkOrderLot is one to many to dbo.TravelerLot.
dboProcess is one to many to dbo.TravelerLot.

 

[edwinjs]

SELECT A.* From
(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) A

Inner Join
(SELECT WorkOrderLot.LotID,ProcNum=Min(Process.ProcNum)
FROM TravelerLot INNER JOIN WorkOrderLot
ON TravelerLot.LotID = WorkOrderLot.LotID
INNER JOIN Process ON TravelerLot.CurrentProcID = Process.ProcessID
GROUP BY WorkOrderLot.LotID) B
On A.LotID = B.LotID
And A.ProcNum = B.ProcNum

 

[edwinjs]

I still think there is a much simpler way to do this.

 

[obislavu]

Try this one:

select TOP 1 * from
(
SELECT dbo.WorkOrderLot.LotID, dbo.TravelerLot.TravLotID, dbo.Process.ProcNum
FROM dbo.TravelerLot INNER JOIN
dbo.WorkOrderLot ON dbo.TravelerLot.LotID = dbo.WorkOrderLot.LotID INNER JOIN
dbo.Process ON dbo.TravelerLot.CurrentProcID = dbo.Process.ProcessID
)
as t1 order by t1.ProcNum

This will return only :

dbo.WorkOrderLot.LotID dbo.TravelerLot.TravLotID dbo.Process.ProcNum
10 9 13

-obislavu-

 

[edwinjs]

obislavu, Your solution works for 1 LotID

 

[Digitalcandy]

Thanks for the help both of you.

edwinjs, your code is on the right track but some LotIDs have the same location and therefore return multiple records.

In the TravelerLot table there are two other fields that need to be accounted for, Closed and TravelerType.

The code should not include any Closed = 0, (Boolean).

TravelerType is a character field. Each TravelerLotID is associated with a TravelerType. If we have 3 TravelerLotIDs in the same location then it would look like so;

LotID = 45 T.L.ID = 2435 TravelerType = GA Process = 5
LotID = 45 T.L.ID = 2440 TravelerType = GB Process = 5
LotID = 45 T.L.ID = 2478 TravelerType = IA Process = 5

In this case I need one record returned and it could be the lowest ascended TravelerType. In the above example it would be the "IA" TravelerType record.

Hope that helps and thanks for the help.