Need help with Select statement

[DavidT_macktool]

I have a table like this:
ShopFloorID = autoincrement key field
ShopNumber = integer
ShopNumberOpen = boolean
PartID = integer
OperationNumber = integer
OperationComplete = boolean

ShopNumber can have Multiple PartId's
PartID can have Multiple OperationNumbers
OperationComplete = False if the operation hasn't been run yet.

I want to Select the first Operation that OperationComplete = False for all part numbers
when ShopNumberOpen = True.

example data in above order:

12342 100 false 1 10 true
12343 100 false 1 20 true
12344 100 false 2 10 true
12345 101 true 1 10 true
12346 101 true 1 20 true
12347 101 true 1 30 true
12348 101 true 1 40 false
12349 102 true 2 10 false
12350 102 true 2 20 false
12351 102 true 2 30 false
12352 103 true 1 10 true
12353 103 true 1 20 false
12354 103 true 1 30 false

select:
12348 101 true 1 40 false
12349 102 true 2 10 false
12353 103 true 1 20 false


"Show me the current OperationNumber for all Part Numbers of open Shop Numbers in the shop."

Can anone help, Combination of Select DISTINCT and Select TOP 1?

 

[TechGnome]

Neither actualy.....

Let's see if I can break down the process needed.

First, start with the simple SELECT (always seems like a good place to start to me).

SELECT * FROM tblShopFloor -- I'm guessing at the table name

That returns everything. Now, we only want those where OperationComplete is false

SELECT * FROM tblShopFloor
WHERE OperationComplete = False

Now add in the ShopOpenNumber criteria:

SELECT * FROM tblShopFloor
WHERE OperationComplete = False
  AND ShopOpenNumber = True

That should give the following results:
12348 101 true 1 40 false
12349 102 true 2 10 false
12350 102 true 2 20 false
12351 102 true 2 30 false
12353 103 true 1 20 false
12354 103 true 1 30 false

Now, we only want the first one for each part id.

SELECT ShopNumber, MIN(OperationNumber) AS MinOpNum
FROM tblShopFloor
WHERE OperationComplete = False
  AND ShopOpenNumber = True
GROUP BY ShopNumber

The results from that should come out like this:
101 40
102 10
103 20

Now we need to tie those results back in.

SELECT SF.* FROM tblShopFloor SF
INNER JOIN (SELECT ShopNumber, MIN(OperationNumber) AS MinOpNum
     FROM tblShopFloor
     WHERE OperationComplete = False
          AND ShopOpenNumber = True
     GROUP BY ShopNumber) SF2
  ON SF.ShopNumber = SF2.ShopNumber
     AND SF.OperationNumber = SF2.MinOpNum
WHERE SF.OperationComplete = False
  AND SF.ShopOpenNumber = True

And that should give you the following results:
12348 101 true 1 40 false
12349 102 true 2 10 false
12353 103 true 1 20 false


Tg

 

[DavidT_macktool]

Can't add a field named description...

'SqlSelectCommand1
'
Me.SqlSelectCommand1.CommandText = 'SELECT [Shop Floor].[Shop Number], MIN([Shop Floor].[Operation Number]) AS Expr1,' & _
' [Shop Floor].[Part ID], [Shop Floor].[Op Complete], [Shop Floor].Priority, [Shop Floor].WorkStation, [Shop Floor].Machine,' & _
' [Shop Floor].[Date to Complete], [Shop Floor].Employee, [Shop Number].Qty, [Shop Number].[Open], Part.[Part Number],' & _
' Part.Purchased FROM [Shop Floor] INNER JOIN [Shop Number] ON [Shop Floor].[ShopNumber] =' & _
' [Shop Number].[Shop Number] INNER JOIN Part ON [Shop Floor].[Part ID] = Part.[Part ID] GROUP BY [Shop Floor].[Shop Number],' & _
' [Shop Floor].[Part ID], [Shop Floor].[Op Complete], [Shop Floor].Priority, [Shop Floor].WorkStation, [Shop Floor].Machine,' & _
' [Shop Floor].[Date to Complete], [Shop Floor].Employee, [Shop Number].Qty, [Shop Number].[Open], Part.[Part Number],' & _
' Part.Purchased HAVING ([Shop Floor].[Op Complete] = 0) AND ([Shop Number].[Open] = 1) AND (Part.Purchased = 0)' & _
' ORDER BY [Shop Floor].[Shop Number], Part.[Part Number]'
Me.SqlSelectCommand1.Connection = Me.SqlConnection1


The above code works, the bad news is when any field named 'description' is added from any table, the MIN function does not work. My description fields are nvarchar - length 25.

Any ideas why i cant include the description field from either the ShopFloor or the Part tables?

P.S. I cleaned up the select statement for display purposes, It works in code.

 

[TechGnome]

Because Min is an aggregate function, any field that isn't also in an aggregate needs to be included in the Group By part of the selection.

Tg

 

[DavidT_macktool]

This statement does not work.

Me.SqlSelectCommand1.CommandText = "SELECT DISTINCT [Shop Floor].[Shop Number], 
MIN([Shop Floor].[Operation Number]) AS OpNumber, 
[Shop Floor].[Part ID], [Shop Floor].[Op Complete], 
[Shop Floor].Priority, [Shop Floor].WorkStation, 
[Shop Floor].Machine, [Shop Floor].[Date to Complete], 
[Shop Floor].Employee, [Shop Number].Qty, 
[Shop Number].[Open], Part.[Part Number], 
Part.Purchased, [Shop Floor].Description 
FROM [Shop Floor] INNER JOIN [Shop Number] ON [Shop Floor].[Shop Number] = [Shop Number].[Shop Number] 
INNER JOIN Part ON [Shop Floor].[Part ID] = Part.[Part ID] 
GROUP BY [Shop Floor].[Shop Number], [Shop Floor].[Part ID], 
[Shop Floor].[Op Complete], [Shop Floor].Priority, 
[Shop Floor].WorkStation, [Shop Floor].Machine, 
[Shop Floor].[Date to Complete], [Shop Floor].Employee, 
[Shop Number].Qty, [Shop Number].[Open], 
Part.[Part Number], Part.Purchased, 
[Shop Floor].Description 
HAVING ([Shop Floor].[Op Complete] = 0) AND ([Shop Number].[Open] = 1) AND (Part.Purchased = 0) 
ORDER BY [Shop Floor].[Shop Number], Part.[Part Number]"

I get a full dataset that conforms to the conditions established by the HAVING clause.
The description field is displayed, heck all fields are displayed, I just do not get the MIN OpNumber record, I get them all.

The weird thing is when I remove the Description field, The MIN function works correctly. All I'm doing is adding the field in the SQLAdapter Query Builder - put a check in the box and verify it is GROUP BY. Then I generate my select statement and generate the dataset. SQL and DATASET schema look normal...but IT DOES NOT WORK.
The description field is necessary to add meaning to the OpNumber field, 10 means nothing - 10 CNC MILL means a lot.

What am I missing/ doing wrong?

 

[TechGnome]

It's because the Description is part of the group by, and since it is different on each line, it groups by all of them..... You'll need to grab it in a different manner....
I've got an idea, I'll need a little time to work it out, but I think I have a solution. Will post later. It involves selecting the description in an inner select with the table joined back to itself, then joined to the above query..... Sounds messy, but it's done all the time.

Tg