Max Join problem

[aspvbnetnerd]

I am trying getting the Max(LastSerialNumber) of a specific scanningstatationid

I want to get the max of lastserialnumber for scannningstationid = 2.
But I am also getting the max of lastserialnumber for scanningstationid 1. I dont want the max value for scanningsationid is 1

The value that I want to get is

3365 0

I have made a picture for you to understand better what my problem is.

http://www40.brinkster.com/aspvbnerd/sql.jpg

Hope you understand what my problem is.

George

 

[bborissov]

I don't know why I can't open the picture.
Try:

SELECT MAX(lastserialnumber) AS lastserialnumber 
       FROM YourTable
WHERE  Scannningstationid = 2

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Qik3Coder]

You are probably displaying more than just that column, so you will have to group by all the other columns.

Select Max(myCol), col2, col 3
from table1
join table2
Group by col2, col3

Please note there are syntax issues with that, it is just there to give an example of what you should be doing.

-Sometimes the answer to your question is the hack that works

 

[aspvbnetnerd]

This is my SQL Code

[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]ISNULL[/color]([COLOR=#FF00FF]MAX[/color](B.LASTSERIALNUMBER), 0), [COLOR=#FF00FF]ISNULL[/color]([COLOR=#FF00FF]MAX[/color](TB.LASTSERIALNUMBER), 0)
[COLOR=blue]FROM[/color]	ASSIGNMENT A 
			[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] CUSTOMER C [COLOR=blue]ON[/color] C.CUSTOMERID = A.CUSTOMERID 
			[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] DOCUMENTTYPE DT [COLOR=blue]ON[/color] DT.DOCUMENTTYPEID = A.DOCUMENTTYPEID 
			[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] CARDBOARD CB [COLOR=blue]ON[/color] CB.ASSIGNMENTID = A.ASSIGNMENTID 
			[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] BUNTCH B [COLOR=blue]ON[/color] CB.CARDBOARDID = B.CARDBOARDID	
			[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] Temp_Buntch TB [COLOR=blue]ON[/color] TB.ASSIGNMENTID = A.ASSIGNMENTID			
[COLOR=blue]WHERE[/color]	A.ASSIGNMENTID = 188
AND		A.ACTIVE = 1
AND		(B.SCANNINGSTATIONID = 2 OR TB.SCANNINGSTATIONID = 2)
AND		B.CARDBOARDID = ([COLOR=blue]SELECT[/color] [COLOR=#FF00FF]MAX[/color](CB.CARDBOARDID)
						 [COLOR=blue]FROM[/color]	ASSIGNMENT A 
							[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] CUSTOMER C [COLOR=blue]ON[/color] C.CUSTOMERID = A.CUSTOMERID 
							[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] DOCUMENTTYPE DT [COLOR=blue]ON[/color] DT.DOCUMENTTYPEID = A.DOCUMENTTYPEID 
							[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] CARDBOARD CB [COLOR=blue]ON[/color] CB.ASSIGNMENTID = A.ASSIGNMENTID 
							[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] BUNTCH B [COLOR=blue]ON[/color] CB.CARDBOARDID = B.CARDBOARDID
							[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] Temp_Buntch TB [COLOR=blue]ON[/color] TB.ASSIGNMENTID = A.ASSIGNMENTID
						 [COLOR=blue]WHERE[/color]		A.ASSIGNMENTID = 188
						 AND		(B.SCANNINGSTATIONID = 2 OR TB.SCANNINGSTATIONID = 2))

I don't se anything wrong with my code. Please help me. :-(

George

 

[Qik3Coder]

Try this instead:

MAX(ISNULL(B.LASTSERIALNUMBER, 0)),

It was probably freaking out with the Isnull on a max value.

-Sometimes the answer to your question is the hack that works

 

[aspvbnetnerd]

Qik3Coder, thank you for trying to help me, but the result is the same.

3365 49620

 

[bborissov]

MAN!
That is terrible!
A several LEFT and INNER joins. SELECT MAX() in the WHERE, NO GROUP BY and you expecting to get right result?

I'll check your query first thing in the morning, here it is 23:00 already and I am so tired to do this.
If nobody suggest better query I'll try to do it.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[aspvbnetnerd]

Why is this terrible? Please tell me so I wont do it again.
I always want to learn what I am making wrong so it would not happen next time.

 

[Qik3Coder]

You are using the same alias on the inner table, which must be at least stressing the query engine.

Start by pulling this out into a variable, then referencing the variable in the main query.

AND        B.CARDBOARDID = (SELECT MAX(CB.CARDBOARDID)
                         FROM    ASSIGNMENT A 
                            INNER JOIN CUSTOMER C ON C.CUSTOMERID = A.CUSTOMERID 
                            INNER JOIN DOCUMENTTYPE DT ON DT.DOCUMENTTYPEID = A.DOCUMENTTYPEID 
                            LEFT JOIN CARDBOARD CB ON CB.ASSIGNMENTID = A.ASSIGNMENTID 
                            LEFT JOIN BUNTCH B ON CB.CARDBOARDID = B.CARDBOARDID
                            LEFT JOIN Temp_Buntch TB ON TB.ASSIGNMENTID = A.ASSIGNMENTID
                         WHERE        A.ASSIGNMENTID = 188
                         AND        (B.SCANNINGSTATIONID = 2 OR TB.SCANNINGSTATIONID = 2))

So that you wind up with something like:

Declare @CardboardID int
set int = --select statement here

--Rest of query here
AND       B.CARDBOARDID = @CardboardID

Once you have verified that the inial query is working you can start checking your main query is tying together correctly.

I usually won't do you job for you, but rather show you how you can figure out how to do what you need to do (next time)


-Sometimes the answer to your question is the hack that works

 

[aspvbnetnerd]

I have changed the alias on the inner table. It is not the same as the above join.

Do you mean I should do like this?

[COLOR=blue]Declare[/color] @CardboardID [COLOR=blue]AS[/color] [COLOR=blue]int[/color]

[COLOR=blue]SELECT[/color] @CardboardID = [COLOR=#FF00FF]MAX[/color](CB.CARDBOARDID)
						 [COLOR=blue]FROM[/color]	ASSIGNMENT A 
							[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] CUSTOMER C [COLOR=blue]ON[/color] C.CUSTOMERID = A.CUSTOMERID 
							[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] DOCUMENTTYPE DT [COLOR=blue]ON[/color] DT.DOCUMENTTYPEID = A.DOCUMENTTYPEID 
							[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] CARDBOARD CB [COLOR=blue]ON[/color] CB.ASSIGNMENTID = A.ASSIGNMENTID 
							[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] BUNTCH B [COLOR=blue]ON[/color] CB.CARDBOARDID = B.CARDBOARDID
							[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] Temp_Buntch TB [COLOR=blue]ON[/color] TB.ASSIGNMENTID = A.ASSIGNMENTID
						 [COLOR=blue]WHERE[/color]		A.ASSIGNMENTID = 188
						 AND		(B.SCANNINGSTATIONID = 2 OR TB.SCANNINGSTATIONID = 2)


[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]MAX[/color]([COLOR=#FF00FF]ISNULL[/color](B.LASTSERIALNUMBER, 0)), [COLOR=#FF00FF]ISNULL[/color]([COLOR=#FF00FF]MAX[/color](TB.LASTSERIALNUMBER), 0)
[COLOR=blue]FROM[/color]	ASSIGNMENT A 
			[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] CUSTOMER C [COLOR=blue]ON[/color] C.CUSTOMERID = A.CUSTOMERID 
			[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] DOCUMENTTYPE DT [COLOR=blue]ON[/color] DT.DOCUMENTTYPEID = A.DOCUMENTTYPEID 
			[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] CARDBOARD CB [COLOR=blue]ON[/color] CB.ASSIGNMENTID = A.ASSIGNMENTID 
			[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] BUNTCH B [COLOR=blue]ON[/color] CB.CARDBOARDID = B.CARDBOARDID	
			[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] Temp_Buntch TB [COLOR=blue]ON[/color] TB.ASSIGNMENTID = A.ASSIGNMENTID			
[COLOR=blue]WHERE[/color]	A.ASSIGNMENTID = 188
AND		A.ACTIVE = 1
AND		(B.SCANNINGSTATIONID = 2 OR TB.SCANNINGSTATIONID = 2)
AND		B.CARDBOARDID = @CardboardID

I don't understand why.

 

[Qik3Coder]

For starts it clarifies what you are trying to do.

Second off, you can debug which part of you code isn't working because now you can display the value that was used in the second query.

Third, that huge block of SQL with a join onto a dynamic table has to take a more resources than tying to a single variable value. I am unsure of the actual gains/losses of doing it this way.

-Sometimes the answer to your question is the hack that works

 

[bborissov]

It is terrible is because you use 6 tables and one derived table just to get two fields?

BTW why not post some simple data and what you want as a result from it. I read and read the query trying to understand it w/o success (Maybe my brain took a free day today )

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[aspvbnetnerd]

I have changed it a little. Now I am getting the right value.
Thank you who tried to help me solve my problem.

From an application the user scannes the firstserialnumber and then the lastserialnumber.
He have a documents that should be places in a cardboard. I have to be shure that the documents are coming in order.

That the firstserialnumber is the same as the lastserialnumber - 1.
Everytime he scannes a document I put the values in a temp tables, because the user might could click cancel button.

I have to validate so that the document comes in order. And if a document is missing I have know the numbers that are missing.

[COLOR=blue]SELECT[/color]	[COLOR=#FF00FF]ISNULL[/color]([COLOR=#FF00FF]MAX[/color](B.LASTSERIALNUMBER), 0), [COLOR=#FF00FF]ISNULL[/color]([COLOR=#FF00FF]MAX[/color](TB.LASTSERIALNUMBER), 0)
[COLOR=blue]FROM[/color]    ASSIGNMENT A 
            [COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] CARDBOARD CB [COLOR=blue]ON[/color] CB.ASSIGNMENTID = A.ASSIGNMENTID 
            [COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] BUNTCH B [COLOR=blue]ON[/color] CB.CARDBOARDID = B.CARDBOARDID    
            [COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] Temp_Buntch TB [COLOR=blue]ON[/color] TB.ASSIGNMENTID = A.ASSIGNMENTID AND B.SCANNINGSTATIONID = TB.SCANNINGSTATIONID          
[COLOR=blue]WHERE[/color]    A.ASSIGNMENTID = 188
AND      A.ACTIVE = 1
AND      (B.SCANNINGSTATIONID = 2 OR TB.SCANNINGSTATIONID = 2)
AND      B.CARDBOARDID = ([COLOR=blue]SELECT[/color]	[COLOR=#FF00FF]MAX[/color](CCBB.CARDBOARDID)
						  [COLOR=blue]FROM[/color]		ASSIGNMENT AA
										[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] CARDBOARD CCBB [COLOR=blue]ON[/color] CCBB.ASSIGNMENTID = AA.ASSIGNMENTID 
										[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] BUNTCH BB [COLOR=blue]ON[/color] CCBB.CARDBOARDID = BB.CARDBOARDID
										[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] Temp_Buntch TTBB [COLOR=blue]ON[/color] TTBB.ASSIGNMENTID = AA.ASSIGNMENTID
							[COLOR=blue]WHERE[/color]       AA.ASSIGNMENTID = 188
							AND        (BB.SCANNINGSTATIONID = 2 OR TTBB.SCANNINGSTATIONID = 2))

Is this better?

George

 

[djj55]

Hello, you might give Qik3Coder's suggestion of rearranging the max/isnull another look. The main reason to have the max on the outside is if max is on the inside and your value is null then the max is null, but if you still want a value then Qik3Coder's suggestion would give it to you the max of what is not null.
djj

 

[aspvbnetnerd]

I never want to have a null value.

Should I use

ISNULL(MAX(B.LASTSERIALNUMBER), 0)

Or

MAX(ISNULL(B.LASTSERIALNUMBER, 0))

 

[Qik3Coder]

you would want the second.
Sometimes it helps to write out what you are after then solve it like a word puzzle.

Follow the logic here.
I want the largest serial number in the list, otherwise i want a 0. I do not want any null values ever.

--Serial Number
b.LastSerialNumber

--Serial Number(s), no null
IsNull(b.LastSerialNumber,0) as LastSerialNumber

--Largest Serial Number
Max(b.LastSerialNumber)

--Largest Serial Number , no null values
Max(IsNull(b.LastSerialNumber,0)) As LastSerialNumber


-Sometimes the answer to your question is the hack that works

 

[aspvbnetnerd]

Thanks for clearing that out for me.

I do not have any more question on this post.

George

 

[djj55]

Qik3Coder, nice explanation.