×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Join Issue

Join Issue

Join Issue

(OP)
Hi

I have a query that is returning the wrong amount of rows. Only by one in this instance. I cant figure our where the joins are incorrect. I have a feeling it is the relationship between dbo.vwWorksSchedule.PackRef = dbo.ProductPack.PackRef I have tried all sorts of joins within the query but cannot work it out. Any advice please. Thanks in advance.

CODE --> sql

SELECT DISTINCT 
                         dbo.WorksOrderHeader.udfType, dbo.Product.Width, dbo.Product.Thickness, dbo.Product.Length, dbo.WorksOrderHeader.udfRunSpeed, dbo.WorksOrderHeader.udfSplitFactor, 
                         dbo.WorksOrderHeader.udfPiecesinSheet, dbo.WorksOrderHeader.udfProfileCode, dbo.Product.ProductCode, dbo.ProductPack.NoOfPieces, dbo.WorksOrderHeader.WorksOrderNumber, 
                         dbo.vwWorksSchedule.Name, dbo.[148-vwWOFinishedProducts].ProductCode AS [Finished Product], dbo.[148-vwWOFinishedProducts].Thickness AS FProdThickness, 
                         dbo.[148-vwWOFinishedProducts].Width AS FProdWidth, dbo.[148-vwWOFinishedProducts].Length AS FProdLength, dbo.[148-vwWOFinishedProducts].DefaultPiecesPerPack, 
                         dbo.WorksOrderSchedule.ScheduleNumber
FROM            dbo.[148-vwWOFinishedProducts] INNER JOIN
                         dbo.vwWorksSchedule INNER JOIN
                         dbo.WorksOrderSchedule ON dbo.vwWorksSchedule.WorksOrderScheduleID = dbo.WorksOrderSchedule.WorksOrderScheduleID INNER JOIN
                         dbo.WorksOrderHeader INNER JOIN
                         dbo.WorksOrderScheduleLine ON dbo.WorksOrderHeader.WorksOrderID = dbo.WorksOrderScheduleLine.WorksOrderID ON 
                         dbo.vwWorksSchedule.WorksOrderScheduleID = dbo.WorksOrderScheduleLine.WorksOrderScheduleID AND dbo.vwWorksSchedule.WOSLineNumber = dbo.WorksOrderScheduleLine.LineNumber AND 
                         dbo.vwWorksSchedule.WOSLineType = dbo.WorksOrderScheduleLine.LineType ON dbo.[148-vwWOFinishedProducts].WorksOrderID = dbo.WorksOrderHeader.WorksOrderID RIGHT OUTER JOIN
                         dbo.Product INNER JOIN
                         dbo.ProductPack ON dbo.Product.ProductID = dbo.ProductPack.ProductID ON dbo.vwWorksSchedule.PackRef = dbo.ProductPack.PackRef FULL OUTER JOIN
                         dbo.AVO ON dbo.Product.ProductID = dbo.AVO.ProductID AND dbo.vwWorksSchedule.AVOName = dbo.AVO.Name
WHERE        (dbo.WorksOrderScheduleLine.LineType = 1) AND (dbo.WorksOrderSchedule.ScheduleNumber = 23301) 

RE: Join Issue

I suspect the issue is related to having outer joins and a WHERE condition that's based on fields on the "some" side of the outer join.

With an outer join, if you want to filter on the side that might not match, you need to move those conditions into the appropriate JOIN/ON. Otherwise, the joins get done and then records that were included because of the outer join get filtered out when they don't match the filter.

Tamar

RE: Join Issue

(OP)
Hi Tamar, thanks for the reply.

I am not the best query writer and this was done in a View. Could you advise where I please the WHERE clause if not where it is. I think that is what you mean.

Thanks

RE: Join Issue

(OP)
I have also tried using Group By and Having but get the same result unfortunately.

RE: Join Issue

You use "Group By and Having" when you have something like SUM(), MAX(), MIN(), AVG(), etc. in your Select statement - which you do not have.


---- Andy

There is a great need for a sarcasm font.

RE: Join Issue

(OP)
Thanks for the tip, as I stated I am not the best query writer. I have reverted back to the WHERE clause but still struggling.

RE: Join Issue

(OP)
Hi

I started afresh and got this far with the View (code attached). This is giving me 3 rows as expected.

I have added a final table in called productpack and need a field out of it called noofpieces. I add the table in and I still have 3 rows. I add the field called noofpieces in and then I get 48 rows.

I am quite baffled now on what to do, could someone please advise how to change the code and where so I can get it working. Thanks

CODE --> SQL

SELECT DISTINCT 
                         dbo.WorksOrderHeader.udfType, dbo.Product.Width, dbo.Product.Thickness, dbo.Product.Length, dbo.WorksOrderHeader.udfRunSpeed, dbo.WorksOrderHeader.udfSplitFactor, 
                         dbo.WorksOrderHeader.udfPiecesinSheet, dbo.WorksOrderHeader.udfProfileCode, dbo.[148-vwWOFinishedProducts].[Source Product], dbo.WorksOrderHeader.WorksOrderNumber, 
                         dbo.[148-WOScheduleView].Machine AS Name, dbo.[148-vwWOFinishedProducts].ProductCode AS [Finished Product], dbo.[148-vwWOFinishedProducts].Thickness AS FProdThickness, 
                         dbo.[148-vwWOFinishedProducts].Width AS FProdWidth, dbo.[148-vwWOFinishedProducts].Length AS FProdLength, dbo.[148-vwWOFinishedProducts].DefaultPiecesPerPack, 
                         dbo.WorksOrderSchedule.ScheduleNumber, dbo.WorksOrderScheduleLine.LineType
FROM            dbo.WorksOrderSchedule INNER JOIN
                         dbo.WorksOrderScheduleLine ON dbo.WorksOrderSchedule.WorksOrderScheduleID = dbo.WorksOrderScheduleLine.WorksOrderScheduleID INNER JOIN
                         dbo.[148-vwWOFinishedProducts] ON dbo.WorksOrderScheduleLine.WorksOrderID = dbo.[148-vwWOFinishedProducts].WorksOrderID INNER JOIN
                         dbo.WorksOrderHeader ON dbo.WorksOrderScheduleLine.WorksOrderID = dbo.WorksOrderHeader.WorksOrderID INNER JOIN
                         dbo.Product ON dbo.[148-vwWOFinishedProducts].ProductID = dbo.Product.ProductID INNER JOIN
                         dbo.[148-WOScheduleView] ON dbo.WorksOrderHeader.WorksOrderID = dbo.[148-WOScheduleView].WorksOrderID INNER JOIN
                         dbo.ProductPack ON dbo.Product.ProductID = dbo.ProductPack.ProductID
WHERE        (dbo.WorksOrderSchedule.ScheduleNumber = 23301) AND (dbo.WorksOrderScheduleLine.LineType = 1) 

RE: Join Issue

Just a guess here...

You may have one record in dbo.Product.ProductID, but 48 records with this ID in dbo.ProductPack.ProductID

And a suggestion:
You use some aliases for your fields, like:
dbo.[148-WOScheduleView].Machine AS Name
and you may consider using aliases for your tables as well. Your Select statement may become a lot shorter and more 'reader friendly' So your dbo.[148-WOScheduleView] may just be WOSV


---- Andy

There is a great need for a sarcasm font.

RE: Join Issue

(OP)
Thank you for the tips.

I have checked the ProductPack table and I can only see relevant records so must be something in the query, which I cannot figure out.

A little lost now, seems frustrating as I only need the one more field. Thanks anyway

RE: Join Issue

(OP)
Hi

Sorry to bother people, but si there anyone who could help with the code for this to make it right, if that is possible.

I have tried practically everything my small knowledge of coding. If I could get the field ProductPack.noofpieces added in and it to only give me the amounts for the 3 rows the code currently gives.

If not thanks anyway.

RE: Join Issue

I'm pretty sure your problem is data, not code. If you're getting 48 records rather than one when you add NoOfPieces to the query, that says that you have data in that field that differs and is preventing DISTINCT from reducing to fewer records. Let me give you a simple example (and I know this is something no one would actually do, but it makes the problem easy to see). Suppose you have table People with this data:

First    Last   Age
John     Smith  45
John     Smith  46
Mary     Jones  27
Mary     Jones  28
Mary     Jones  29 

If you issue:

SELECT DISTINCT First, Last FROM People 

you'll get two records, but if you change it to:

SELECT DISTINCT First, Last, Age FROM People 

you'll get 5.

Tamar

RE: Join Issue

You need to have an intimate knowledge of your DATA and how your tables relate.

Redo your query by joining the two tables most closely related and running. Then add a table at a time, running your query. You'll soon discover the table with the DATA that is causing additional row(s).

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Join Issue

(OP)
Hi

SkipVought I did that. It is the ProductPack table that creates the extra rows as soon as I add a filed in from the table. I will examine the tables again today and update.

Thanks for the reply's it is appreciated.

RE: Join Issue

Before you added dbo.ProductPack table and had 3 records...
You display several field from dbo.Product (Width, Thickness, Length).
Add to it dbo.Product.ProductID What values do you see in your 3 records? Let's assume you have 123, 456, and 475

Go to your dbo.ProductPack table and do this:

Select DISTINCT ProductID, noofpieces
From dbo.ProductPack
Where ProductID IN (123, 456, 475)

What do you see? How many records do you get?


---- Andy

There is a great need for a sarcasm font.

RE: Join Issue

(OP)
Hi Andy

Doing what you say I get back 48 rows returned back.

Thanks



RE: Join Issue

OK, now we are getting somewhere....
So now you know that there is either one or more than just one noofpieces value (record) for any ProductID in dbo.ProductPack table (like I stated before). When you get 48 records from your request, that IS the correct number of records.

How do you want to show all of that data?
If you still have your heart set on just the 3 records displayed, somebody needs to decided which 3 out of 48 noofpieces to show....


---- Andy

There is a great need for a sarcasm font.

RE: Join Issue

(OP)
Yes thank you for making it clearer for me.

We just want to see the noofpieces, for a specific schedule number which can have several worksorder numbers on it.

So Schedule number 23301 as 3 works worksordernumbers. I only need to see the total noofpieces for these 3. The only place this is held on the database is in ProductPack table.

The schedule number would change but I am using this one for now to try and get the mechanics working.

RE: Join Issue

You already have and display ScheduleNumber field, so you can either add dbo.ProductPack table to your Select, have SUM(noofpieces) field, and GROUP BY all other fields in your Select statement, or you can do:

SELECT DISTINCT
dbo.WorksOrderHeader.udfType, ...,
(SELECT SUM(noofpieces) from dbo.ProductPack Where ScheduleNumber = 23301) As SumOfnoofpieces

FROM dbo.WorksOrderSchedule INNER JOIN ...


---- Andy

There is a great need for a sarcasm font.

RE: Join Issue

(OP)



Hi

I have added in the SUM and the code is like the below code inserted. However it appears to be adding up all the rows and gives the same sum for all 3 rows. Sum of 371456344.0000 which is nothing like I am expecting figures should be something like 20, 30, 15. I could try the other way but this would be Monday and I can update then. Thanks for the help and patience on this.

CODE --> sql

SELECT DISTINCT 
                         dbo.WorksOrderHeader.udfType, dbo.Product.Width, dbo.Product.Thickness, dbo.Product.Length, dbo.WorksOrderHeader.udfRunSpeed, dbo.WorksOrderHeader.udfSplitFactor, 
                         dbo.WorksOrderHeader.udfPiecesinSheet, dbo.WorksOrderHeader.udfProfileCode, dbo.[148-vwWOFinishedProducts].[Source Product], dbo.WorksOrderHeader.WorksOrderNumber, 
                         dbo.[148-WOScheduleView].Machine AS Name, dbo.[148-vwWOFinishedProducts].ProductCode AS [Finished Product], dbo.[148-vwWOFinishedProducts].Thickness AS FProdThickness, 
                         dbo.[148-vwWOFinishedProducts].Width AS FProdWidth, dbo.[148-vwWOFinishedProducts].Length AS FProdLength, dbo.[148-vwWOFinishedProducts].DefaultPiecesPerPack, 
                         dbo.WorksOrderSchedule.ScheduleNumber, dbo.WorksOrderScheduleLine.LineType, dbo.Product.ProductID, 
						 (SELECT SUM(noofpieces) from dbo.ProductPack Where dbo.WorksOrderSchedule.ScheduleNumber = 23301) As SumOfnoofpieces
FROM            dbo.WorksOrderSchedule INNER JOIN
                         dbo.WorksOrderScheduleLine ON dbo.WorksOrderSchedule.WorksOrderScheduleID = dbo.WorksOrderScheduleLine.WorksOrderScheduleID INNER JOIN
                         dbo.[148-vwWOFinishedProducts] ON dbo.[148-vwWOFinishedProducts].WorksOrderID = dbo.WorksOrderScheduleLine.WorksOrderID INNER JOIN
                         dbo.WorksOrderHeader ON dbo.WorksOrderScheduleLine.WorksOrderID = dbo.WorksOrderHeader.WorksOrderID INNER JOIN
                         dbo.Product ON dbo.Product.ProductID = dbo.[148-vwWOFinishedProducts].ProductID INNER JOIN
                         dbo.[148-WOScheduleView] ON dbo.[148-WOScheduleView].WorksOrderID = dbo.WorksOrderHeader.WorksOrderID INNER JOIN
                         dbo.ProductPack ON dbo.ProductPack.ProductID = dbo.Product.ProductID
WHERE        (dbo.WorksOrderSchedule.ScheduleNumber = 23301) AND (dbo.WorksOrderScheduleLine.LineType = 1) 

RE: Join Issue

Just a shot in the dark here...

CODE

SELECT DISTINCT 
dbo.WorksOrderHeader.udfType, 
dbo.Product.Width, 
dbo.Product.Thickness, 
dbo.Product.Length, 
dbo.WorksOrderHeader.udfRunSpeed, 
dbo.WorksOrderHeader.udfSplitFactor, 
dbo.WorksOrderHeader.udfPiecesinSheet, 
dbo.WorksOrderHeader.udfProfileCode, 
dbo.[148-vwWOFinishedProducts].[Source Product], 
dbo.WorksOrderHeader.WorksOrderNumber, 
dbo.[148-WOScheduleView].Machine AS Name, 
dbo.[148-vwWOFinishedProducts].ProductCode AS [Finished Product], 
dbo.[148-vwWOFinishedProducts].Thickness AS FProdThickness, 
dbo.[148-vwWOFinishedProducts].Width AS FProdWidth, 
dbo.[148-vwWOFinishedProducts].Length AS FProdLength, 
dbo.[148-vwWOFinishedProducts].DefaultPiecesPerPack, 
dbo.WorksOrderSchedule.ScheduleNumber, 
dbo.WorksOrderScheduleLine.LineType, 
dbo.Product.ProductID, 
SUM(dbo.ProductPack.noofpieces) As SumOfNoOfPieces
FROM dbo.WorksOrderSchedule INNER JOIN
dbo.WorksOrderScheduleLine ON dbo.WorksOrderSchedule.WorksOrderScheduleID = dbo.WorksOrderScheduleLine.WorksOrderScheduleID INNER JOIN
dbo.[148-vwWOFinishedProducts] ON dbo.[148-vwWOFinishedProducts].WorksOrderID = dbo.WorksOrderScheduleLine.WorksOrderID INNER JOIN
dbo.WorksOrderHeader ON dbo.WorksOrderScheduleLine.WorksOrderID = dbo.WorksOrderHeader.WorksOrderID INNER JOIN
dbo.Product ON dbo.Product.ProductID = dbo.[148-vwWOFinishedProducts].ProductID INNER JOIN
dbo.[148-WOScheduleView] ON dbo.[148-WOScheduleView].WorksOrderID = dbo.WorksOrderHeader.WorksOrderID INNER JOIN
dbo.ProductPack ON dbo.ProductPack.ProductID = dbo.Product.ProductID
INNER JOIN dbo.ProductPack ON dbo.Product.ProductID = dbo.ProductPack.ProductID
WHERE (dbo.WorksOrderSchedule.ScheduleNumber = 23301) 
AND (dbo.WorksOrderScheduleLine.LineType = 1) 
GROUP BY 
dbo.WorksOrderHeader.udfType, 
dbo.Product.Width, 
dbo.Product.Thickness, 
dbo.Product.Length, 
dbo.WorksOrderHeader.udfRunSpeed, 
dbo.WorksOrderHeader.udfSplitFactor, 
dbo.WorksOrderHeader.udfPiecesinSheet, 
dbo.WorksOrderHeader.udfProfileCode, 
dbo.[148-vwWOFinishedProducts].[Source Product], 
dbo.WorksOrderHeader.WorksOrderNumber, 
dbo.[148-WOScheduleView].Machine, 
dbo.[148-vwWOFinishedProducts].ProductCode, 
dbo.[148-vwWOFinishedProducts].Thickness, 
dbo.[148-vwWOFinishedProducts].Width, 
dbo.[148-vwWOFinishedProducts].Length, 
dbo.[148-vwWOFinishedProducts].DefaultPiecesPerPack, 
dbo.WorksOrderSchedule.ScheduleNumber, 
dbo.WorksOrderScheduleLine.LineType, 
dbo.Product.ProductID 


---- Andy

There is a great need for a sarcasm font.

RE: Join Issue

(OP)
Hi

I get this when I ran yours

Msg 1013, Level 16, State 1, Line 1
The objects "dbo.ProductPack" and "dbo.ProductPack" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

RE: Join Issue

Ooops, that's because this line is in there twice (that's what you get by shooting in the dark... laser)

CODE

SELECT DISTINCT 
dbo.WorksOrderHeader.udfType, 
dbo.Product.Width, 
dbo.Product.Thickness, 
dbo.Product.Length, 
dbo.WorksOrderHeader.udfRunSpeed, 
dbo.WorksOrderHeader.udfSplitFactor, 
dbo.WorksOrderHeader.udfPiecesinSheet, 
dbo.WorksOrderHeader.udfProfileCode, 
dbo.[148-vwWOFinishedProducts].[Source Product], 
dbo.WorksOrderHeader.WorksOrderNumber, 
dbo.[148-WOScheduleView].Machine AS Name, 
dbo.[148-vwWOFinishedProducts].ProductCode AS [Finished Product], 
dbo.[148-vwWOFinishedProducts].Thickness AS FProdThickness, 
dbo.[148-vwWOFinishedProducts].Width AS FProdWidth, 
dbo.[148-vwWOFinishedProducts].Length AS FProdLength, 
dbo.[148-vwWOFinishedProducts].DefaultPiecesPerPack, 
dbo.WorksOrderSchedule.ScheduleNumber, 
dbo.WorksOrderScheduleLine.LineType, 
dbo.Product.ProductID, 
SUM(dbo.ProductPack.noofpieces) As SumOfNoOfPieces

FROM dbo.WorksOrderSchedule INNER JOIN
dbo.WorksOrderScheduleLine ON dbo.WorksOrderSchedule.WorksOrderScheduleID = dbo.WorksOrderScheduleLine.WorksOrderScheduleID INNER JOIN
dbo.[148-vwWOFinishedProducts] ON dbo.[148-vwWOFinishedProducts].WorksOrderID = dbo.WorksOrderScheduleLine.WorksOrderID INNER JOIN
dbo.WorksOrderHeader ON dbo.WorksOrderScheduleLine.WorksOrderID = dbo.WorksOrderHeader.WorksOrderID INNER JOIN
dbo.Product ON dbo.Product.ProductID = dbo.[148-vwWOFinishedProducts].ProductID INNER JOIN
dbo.[148-WOScheduleView] ON dbo.[148-WOScheduleView].WorksOrderID = dbo.WorksOrderHeader.WorksOrderID 
INNER JOIN dbo.ProductPack ON dbo.ProductPack.ProductID = dbo.Product.ProductID
INNER JOIN dbo.ProductPack ON dbo.Product.ProductID = dbo.ProductPack.ProductID
WHERE (dbo.WorksOrderSchedule.ScheduleNumber = 23301) 
AND (dbo.WorksOrderScheduleLine.LineType = 1) 

GROUP BY 
dbo.WorksOrderHeader.udfType, 
dbo.Product.Width, 
dbo.Product.Thickness, 
dbo.Product.Length, 
dbo.WorksOrderHeader.udfRunSpeed, 
dbo.WorksOrderHeader.udfSplitFactor, 
dbo.WorksOrderHeader.udfPiecesinSheet, 
dbo.WorksOrderHeader.udfProfileCode, 
dbo.[148-vwWOFinishedProducts].[Source Product], 
dbo.WorksOrderHeader.WorksOrderNumber, 
dbo.[148-WOScheduleView].Machine, 
dbo.[148-vwWOFinishedProducts].ProductCode, 
dbo.[148-vwWOFinishedProducts].Thickness, 
dbo.[148-vwWOFinishedProducts].Width, 
dbo.[148-vwWOFinishedProducts].Length, 
dbo.[148-vwWOFinishedProducts].DefaultPiecesPerPack, 
dbo.WorksOrderSchedule.ScheduleNumber, 
dbo.WorksOrderScheduleLine.LineType, 
dbo.Product.ProductID 


---- Andy

There is a great need for a sarcasm font.

RE: Join Issue

(OP)
Hi

Ok tried this and I am getting some strange figures for the sumofnopieces, image attached




In my first attempt at this query I only got 2 lines but had the correct noofpieces (code below). However it left one row out that does not have any figures in for the field productpack.noofpieces which I would want to see, I tried IsNUll on this to try and put 0 in to make the row show but it did not work. The reason I am showing this code is that I am sure we have used the sum on the right field so don't understand why we get the figures using the sum and in the below query I get 30 then 20 and then one missing row. Thanks

CODE --> sql

SELECT DISTINCT 
                         dbo.WorksOrderHeader.udfType, dbo.Product.Width, dbo.Product.Thickness, dbo.Product.Length, dbo.WorksOrderHeader.udfRunSpeed, dbo.WorksOrderHeader.udfSplitFactor, 
                         dbo.WorksOrderHeader.udfPiecesinSheet, dbo.WorksOrderHeader.udfProfileCode, dbo.Product.ProductCode, dbo.ProductPack.NoOfPieces, dbo.WorksOrderHeader.WorksOrderNumber, 
                         dbo.vwWorksSchedule.Name, dbo.[148-vwWOFinishedProducts].ProductCode AS [Finished Product], dbo.[148-vwWOFinishedProducts].Thickness AS FProdThickness, 
                         dbo.[148-vwWOFinishedProducts].Width AS FProdWidth, dbo.[148-vwWOFinishedProducts].Length AS FProdLength, dbo.[148-vwWOFinishedProducts].DefaultPiecesPerPack, 
                         dbo.WorksOrderSchedule.ScheduleNumber
FROM            dbo.[148-vwWOFinishedProducts] INNER JOIN
                         dbo.vwWorksSchedule INNER JOIN
                         dbo.WorksOrderSchedule ON dbo.vwWorksSchedule.WorksOrderScheduleID = dbo.WorksOrderSchedule.WorksOrderScheduleID INNER JOIN
                         dbo.WorksOrderHeader INNER JOIN
                         dbo.WorksOrderScheduleLine ON dbo.WorksOrderHeader.WorksOrderID = dbo.WorksOrderScheduleLine.WorksOrderID ON 
                         dbo.vwWorksSchedule.WorksOrderScheduleID = dbo.WorksOrderScheduleLine.WorksOrderScheduleID AND dbo.vwWorksSchedule.WOSLineNumber = dbo.WorksOrderScheduleLine.LineNumber AND 
                         dbo.vwWorksSchedule.WOSLineType = dbo.WorksOrderScheduleLine.LineType ON dbo.[148-vwWOFinishedProducts].WorksOrderID = dbo.WorksOrderHeader.WorksOrderID RIGHT OUTER JOIN
                         dbo.Product INNER JOIN
                         dbo.ProductPack ON dbo.Product.ProductID = dbo.ProductPack.ProductID ON dbo.vwWorksSchedule.PackRef = dbo.ProductPack.PackRef FULL OUTER JOIN
                         dbo.AVO ON dbo.Product.ProductID = dbo.AVO.ProductID AND dbo.vwWorksSchedule.AVOName = dbo.AVO.Name
WHERE        (dbo.WorksOrderScheduleLine.LineType = 1) AND (dbo.WorksOrderSchedule.ScheduleNumber = 23301) 

RE: Join Issue

"In my first attempt at this query I only got 2 lines" - that's OK. That means one table (dbo.Product) has a record with that ProductID, but the other table (dbo.ProductPack) does NOT. You have a hint about it: "it left one row out that does not have any figures in for the field productpack.noofpieces which I would want to see"

Which means instead of INNER JOIN you need to use LEFT or RIGHT JOIN to join the two tables.

To help you visualize different JOINTs, take a look at this picture

---- Andy

There is a great need for a sarcasm font.

RE: Join Issue

(OP)
Hi

I di try to change the Joins in this part but I either got both rows still and not 3, or none at all. I tried all combinations but no luck. Thanks

dbo.Product INNER JOIN
dbo.ProductPack ON dbo.Product.ProductID = dbo.ProductPack.ProductID ON dbo.vwWorksSchedule.PackRef = dbo.ProductPack.PackRef FULL OUTER JOIN

RE: Join Issue

When you get 2 rows of data, do you get correct values in SumOfNoOfPieces field?
If so, what is the Select statement you use to get the 2 rows?


---- Andy

There is a great need for a sarcasm font.

RE: Join Issue

(OP)
Hi

Yes I get the right SUM and the code is this below (posted in earlier post)

CODE --> sql

SELECT DISTINCT 
                         dbo.WorksOrderHeader.udfType, dbo.Product.Width, dbo.Product.Thickness, dbo.Product.Length, dbo.WorksOrderHeader.udfRunSpeed, dbo.WorksOrderHeader.udfSplitFactor, 
                         dbo.WorksOrderHeader.udfPiecesinSheet, dbo.WorksOrderHeader.udfProfileCode, dbo.Product.ProductCode, dbo.ProductPack.NoOfPieces, dbo.WorksOrderHeader.WorksOrderNumber, 
                         dbo.vwWorksSchedule.Name, dbo.[148-vwWOFinishedProducts].ProductCode AS [Finished Product], dbo.[148-vwWOFinishedProducts].Thickness AS FProdThickness, 
                         dbo.[148-vwWOFinishedProducts].Width AS FProdWidth, dbo.[148-vwWOFinishedProducts].Length AS FProdLength, dbo.[148-vwWOFinishedProducts].DefaultPiecesPerPack, 
                         dbo.WorksOrderSchedule.ScheduleNumber
FROM            dbo.[148-vwWOFinishedProducts] INNER JOIN
                         dbo.vwWorksSchedule INNER JOIN
                         dbo.WorksOrderSchedule ON dbo.vwWorksSchedule.WorksOrderScheduleID = dbo.WorksOrderSchedule.WorksOrderScheduleID INNER JOIN
                         dbo.WorksOrderHeader INNER JOIN
                         dbo.WorksOrderScheduleLine ON dbo.WorksOrderHeader.WorksOrderID = dbo.WorksOrderScheduleLine.WorksOrderID ON 
                         dbo.vwWorksSchedule.WorksOrderScheduleID = dbo.WorksOrderScheduleLine.WorksOrderScheduleID AND dbo.vwWorksSchedule.WOSLineNumber = dbo.WorksOrderScheduleLine.LineNumber AND 
                         dbo.vwWorksSchedule.WOSLineType = dbo.WorksOrderScheduleLine.LineType ON dbo.[148-vwWOFinishedProducts].WorksOrderID = dbo.WorksOrderHeader.WorksOrderID RIGHT OUTER JOIN
                         dbo.Product INNER JOIN
                         dbo.ProductPack ON dbo.Product.ProductID = dbo.ProductPack.ProductID ON dbo.vwWorksSchedule.PackRef = dbo.ProductPack.PackRef FULL OUTER JOIN
                         dbo.AVO ON dbo.Product.ProductID = dbo.AVO.ProductID AND dbo.vwWorksSchedule.AVOName = dbo.AVO.Name
WHERE        (dbo.WorksOrderScheduleLine.LineType = 1) AND (dbo.WorksOrderSchedule.ScheduleNumber = 23301) 

RE: Join Issue

No, that's not it.
There is no SUM() or GROUP BY in this statement....


---- Andy

There is a great need for a sarcasm font.

RE: Join Issue

(OP)
Hi

Ok I did it using the SUM and then I still only get 2 rows and also the SUM is incorrect in the second row, what was 20 is now 380. Code is below.

CODE --> sql

SELECT DISTINCT 
                         dbo.WorksOrderHeader.udfType, 
						 dbo.Product.Width, 
						 dbo.Product.Thickness, 
						 dbo.Product.Length, 
						 dbo.WorksOrderHeader.udfRunSpeed, 
						 dbo.WorksOrderHeader.udfSplitFactor, 
                         dbo.WorksOrderHeader.udfPiecesinSheet, 
						 dbo.WorksOrderHeader.udfProfileCode, 
						 dbo.Product.ProductCode, 
						 SUM(dbo.ProductPack.NoOfPieces) As SumOfNoOfPieces, 
						 dbo.WorksOrderHeader.WorksOrderNumber, 
                         dbo.vwWorksSchedule.Name, 
						 dbo.[148-vwWOFinishedProducts].ProductCode AS [Finished Product], 
						 dbo.[148-vwWOFinishedProducts].Thickness AS FProdThickness, 
                         dbo.[148-vwWOFinishedProducts].Width AS FProdWidth, 
						 dbo.[148-vwWOFinishedProducts].Length AS FProdLength, 
						 dbo.[148-vwWOFinishedProducts].DefaultPiecesPerPack, 
                         dbo.WorksOrderSchedule.ScheduleNumber
FROM            dbo.[148-vwWOFinishedProducts] INNER JOIN
                         dbo.vwWorksSchedule INNER JOIN
                         dbo.WorksOrderSchedule ON dbo.vwWorksSchedule.WorksOrderScheduleID = dbo.WorksOrderSchedule.WorksOrderScheduleID INNER JOIN
                         dbo.WorksOrderHeader INNER JOIN
                         dbo.WorksOrderScheduleLine ON dbo.WorksOrderHeader.WorksOrderID = dbo.WorksOrderScheduleLine.WorksOrderID ON 
                         dbo.vwWorksSchedule.WorksOrderScheduleID = dbo.WorksOrderScheduleLine.WorksOrderScheduleID AND dbo.vwWorksSchedule.WOSLineNumber = dbo.WorksOrderScheduleLine.LineNumber AND 
                         dbo.vwWorksSchedule.WOSLineType = dbo.WorksOrderScheduleLine.LineType ON dbo.[148-vwWOFinishedProducts].WorksOrderID = dbo.WorksOrderHeader.WorksOrderID RIGHT OUTER JOIN
                         dbo.Product INNER JOIN
                         dbo.ProductPack ON dbo.Product.ProductID = dbo.ProductPack.ProductID ON dbo.vwWorksSchedule.PackRef = dbo.ProductPack.PackRef FULL OUTER JOIN
                         dbo.AVO ON dbo.Product.ProductID = dbo.AVO.ProductID AND dbo.vwWorksSchedule.AVOName = dbo.AVO.Name
WHERE        (dbo.WorksOrderScheduleLine.LineType = 1) 
AND (dbo.WorksOrderSchedule.ScheduleNumber = 23301) 
Group BY
                         dbo.WorksOrderHeader.udfType, 
						 dbo.Product.Width, 
						 dbo.Product.Thickness, 
						 dbo.Product.Length, 
						 dbo.WorksOrderHeader.udfRunSpeed, 
						 dbo.WorksOrderHeader.udfSplitFactor, 
                         dbo.WorksOrderHeader.udfPiecesinSheet, 
						 dbo.WorksOrderHeader.udfProfileCode, 
						 dbo.Product.ProductCode, 
						 dbo.ProductPack.NoOfPieces,
						 dbo.WorksOrderHeader.WorksOrderNumber, 
                         dbo.vwWorksSchedule.Name, 
						 dbo.[148-vwWOFinishedProducts].ProductCode, 
						 dbo.[148-vwWOFinishedProducts].Thickness , 
                         dbo.[148-vwWOFinishedProducts].Width , 
						 dbo.[148-vwWOFinishedProducts].Length , 
						 dbo.[148-vwWOFinishedProducts].DefaultPiecesPerPack, 
                         dbo.WorksOrderSchedule.ScheduleNumber 

RE: Join Issue

Sorry, cannot help you.
Maybe somebody smarter than me jumps in and give you some suggestions.

Below, in red are some of 'questionable' joints, or lack of them, although I may be wrong about it. I don't really know your tables' structures and relations.

CODE

...
      FROM dbo.[148-vwWOFinishedProducts] 
INNER JOIN dbo.vwWorksSchedule 
    ON WHAT FIELDS?
INNER JOIN dbo.WorksOrderSchedule 
    ON dbo.vwWorksSchedule.WorksOrderScheduleID    = dbo.WorksOrderSchedule.WorksOrderScheduleID 
INNER JOIN dbo.WorksOrderHeader 
    ON WHAT FIELDS?
INNER JOIN dbo.WorksOrderScheduleLine 
    ON dbo.WorksOrderHeader.WorksOrderID           = dbo.WorksOrderScheduleLine.WorksOrderID 
    ON dbo.vwWorksSchedule.WorksOrderScheduleID    = dbo.WorksOrderScheduleLine.WorksOrderScheduleID 
   AND dbo.vwWorksSchedule.WOSLineNumber           = dbo.WorksOrderScheduleLine.LineNumber 
   AND dbo.vwWorksSchedule.WOSLineType             = dbo.WorksOrderScheduleLine.LineType 
    ON dbo.[148-vwWOFinishedProducts].WorksOrderID = dbo.WorksOrderHeader.WorksOrderID 
RIGHT OUTER JOIN dbo.Product 
    ON WHAT FIELDS?
INNER JOIN dbo.ProductPack 
    ON dbo.Product.ProductID       = dbo.ProductPack.ProductID 
    ON dbo.vwWorksSchedule.PackRef = dbo.ProductPack.PackRef 
FULL OUTER JOIN dbo.AVO 
    ON dbo.Product.ProductID       = dbo.AVO.ProductID 
   AND dbo.vwWorksSchedule.AVOName = dbo.AVO.Name
... 


---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close