Grouping help please 3

[Digitalcandy]

MS SQL 2000

I have two tables, dbo.TravelerLot and dbo.Process. Process has a "many to one" relationship with TravelerLot.



dbo.TravelerLot INNER JOIN
dbo.Process ON dbo.TravelerLot.CurrentProcID = dbo.Process.ProcessID




I am trying to make a view that has grouping. I'm having a little trouble with the code to make the grouping the way I want.

Without grouping I have something like so;

dbo.TravelerLot.LotID dbo.TravelerLot.TravLotID dbo.Process.ProcNum
1 100 5
1 101 10
1 102 3
1 103 30
2 220 500
2 210 23
2 240 400
3 45 100
3 46 90



With the propper grouping I'd like my view to look like the example below based on the data example above, (data is returned based on the lowest ProcNum);


dbo.TravelerLot.LotID dbo.TravelerLot.TravLotID dbo.Process.ProcNum
1 102 3
2 210 23
3 46 90





I need the grouping to happen based on TravelerLot.LotID, however, I also need TravelerLot.TravLotID to be visible but it needs to return the the TravLotID that has the lowest Process.ProcNum value.

Can one of you experts make this happen? I don't know much about SQL programming but this would really help for report writing.

TIA.

 

[whatduh00]

try this one out

select dbo.TravelerLot.LotID, dbo.TravelerLot.TravLotID, min(dbo.Process.ProcNum)
from dbo.TravelerLot
inner join dbo.Process on
dbo.Process.ProcNum = dbo.TravelerLot.ProcId
group by dbo.TravelerLot.LotID, dbo.TravelerLot.TravLotID

 

[Digitalcandy]

Thanks for trying. Your code returns all the records in TravelerLot but returns the lowest value ProcNum for TravelerLot.LotID.

Your example would look like this;

dbo.TravelerLot.LotID dbo.TravelerLot.TravLotID dbo.Process.ProcNum
1 100 3
1 101 3
1 102 3
1 103 3
2 220 23
2 210 23
2 240 23
3 45 90
3 46 90




I must add that TravelerLot.TravLotID is the table key in TravelerLot

 

[amorous]

Does this work:

SELECT t1.LotID, t1.TravLotID,t2.ProcNum
FROM dbo.TravelerLot t1
INNER JOIN dbo.Process t2
ON
t2.ProcNum = t1.ProcId
WHERE t2.ProcNum In (select top 1 t2.ProcNum from dbo.Process where t2.ProcNum = t1.ProcId ORDER BY t2.ProcNum Asc);

-VJ

 

[Digitalcandy]

Your code returns the same type of view as whatduh00 code. So it doesn't work for me. Thanks.

 

[amorous]

What abt this:

SELECT TOP 1 t1.LotID, t1.TravLotID,t2.ProcNum
FROM dbo.TravelerLot t1
INNER JOIN dbo.Process t2
ON
t2.ProcNum = t1.ProcId
WHERE t2.ProcNum In (select top 1 t2.ProcNum from dbo.Process where t2.ProcNum = t1.ProcId ORDER BY t2.ProcNum Asc);


-VJ

 

[amorous]

ignore my previous post...its not going to give the result you want...

-VJ

 

[Digitalcandy]

After tweaking some of the field names in your code and getting the view to run correctly, it only returns 1 record, the top 1 LotID.

 

[Pattycake245]

How about this:

SELECT a.LotID, a.TravLotID, b.ProcNum
FROM dbo.TravelerLot a
JOIN dbo.Process b
ON
a.ProcId = b.ProcNum
WHERE b.ProcNum In (select min(ProcNum) from dbo.Process where LotID=a.LotID)

Tim

 

[Wyldcard9]

Why don't you try using the 'DISTINCT' key word? I am not sure if that is the right placement of it or not.

SELECT DISTINCT t1.LotID, t1.TravLotID,t2.ProcNum
FROM dbo.TravelerLot t1
INNER JOIN dbo.Process t2
ON
t2.ProcNum = t1.ProcId
WHERE t2.ProcNum In (select top 1 t2.ProcNum from dbo.Process where t2.ProcNum = t1.ProcId ORDER BY t2.ProcNum Asc);

 

[Digitalcandy]

Wow, I really appreciate the responses but none have given the results I'm looking for.

Each LotID could have many TravLotIDs. Those TravLotIDs have locations in our production floor, (ProcNum). I need a view that shows the lowest location number for a LotID.

For instance;

Let's say I'm manufacturing cars. The car itself would be the LotID. Each part of the car would be the TravLotID. During production my TravLotIDs, (car parts), would be at a different location on my production floor being assembled. If our assembly process numbers are in sequence, (in terms of the flow of building the car), then the parts that have the lowest process number could give me a good indication as to how much longer it will take to complete the car.

For example;

Car ABC / LotID = 1
Part = Engine / TravLotID = 100
Location = Piston assembly / ProcNum = 5


Car ABC / LotID = 1
Part = Driver Door / TravLotID = 101
Location = Window assembly / ProcNum = 10


Car ABC / LotID = 1
Part = Transmission / TravLotID = 102
Location = Installing Pressure Plates / ProcNum = 3



From this information the view would return the record below because it has the lowest ProcNum;

dbo.TravelerLot.LotID dbo.TravelerLot.TravLotID dbo.Process.ProcNum
1 102 3





Please keep in mind that TravLotID is the unique key in table TravelerLot

 

[Pattycake245]

SELECT a.LotID, a.TravLotID, b.ProcNum
FROM dbo.TravelerLot a
JOIN dbo.Process b
ON
a.CurrentProcID = b.ProcessID
WHERE b.ProcNum In (select min(ProcNum) from dbo.Process where ProcessID=b.ProcessID)

Tim

If this is no good, maybe you could show a small sample of the two tables and the values in each. I may be missing something. I assume the processid and currentprocid are unique in both tables?

 

[Digitalcandy]

PattyCake,

In table TravelerLot, TravLotID is the unique key.

In table Process, ProcessID is the unique key.

Your code above will not work with your assumptions. Also, I did post example of data in my first post and then followed it up with the best logical representation I could using the "Car" manufacturing example.

 

[Pattycake245]

So the common fields to join the two tables are TravLotID and ProcessID?

Tim

 

[Digitalcandy]

No

Here is how the two tables are joined using SQL code;


dbo.TravelerLot INNER JOIN
dbo.Process ON dbo.TravelerLot.CurrentProcID = dbo.Process.ProcessID

 

[amorous]

Yes Tim,

I guess we are missing some information here....

I emulated his tables in the below format...

TravelerLot

ProcId|LotId|TravLotId
________________________
1001| 1| 101
1002| 1| 102
1003| 1| 103
1004| 1| 104
1005| 2| 210
1006| 2| 220
1007| 2| 240
1008| 3| 45
1009| 3| 46

Process

ProcId|ProcNum
______________
1001 | 5
1002 | 10
1003 | 3
1004 | 30
1005 | 500
1006 | 23
1007 | 400
1008 | 100
1009 | 90


And when we run the query you, me and whatduh suggested results in

1 | 103 | 3

the thing is that we have to loop thru the LotId's

-VJ

 

[amorous]

Sorry

Table Process has

ProcessId | ProcNum



-VJ

 

[Pattycake245]

Can you post you entire query you have so far? That may be the best place to start

Can I assume that with all the extra columns taken out, the tables would look like this:

TravelerLot

CurrentProcID LotID TravLotID
1 1 100
2 1 101
3 1 102
4 1 103....


Process


ProcessID ProcNum
1 5
2 10
3 3
4 30

 

[Digitalcandy]

amorous,

You have the layout correct, however, the bolded items are corrections if we are being exact about things.

TravelerLot

CurrentProcId|LotId|TravLotId
________________________
1001| 1| 101
1002| 1| 102
1003| 1| 103
1004| 1| 104
1005| 2| 210
1006| 2| 220
1007| 2| 240
1008| 3| 45
1009| 3| 46

Process

ProcessId|ProcNum
______________
1001 | 5
1002 | 10
1003 | 3
1004 | 30
1005 | 500
1006 | 23
1007 | 400
1008 | 100
1009 | 90

 

[Digitalcandy]

PattyCake, you got it!