Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Join Issue 1

Status
Not open for further replies.
Cpreston

Cpreston

MIS
Mar 4, 2015
972
GB
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.

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)
 
Sort by date Sort by votes
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.
 
Upvote 0 Downvote
Ooops, that's because [red]this line[/red] 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[red]
[s]INNER JOIN dbo.ProductPack ON dbo.Product.ProductID = dbo.ProductPack.ProductID[/s][/red]
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.
 
Upvote 0 Downvote
Hi

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

Capture_tc8ude.jpg



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

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)
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
When you get 2 rows of data, do you get correct values in [tt]SumOfNoOfPieces[/tt] 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.
 
Upvote 0 Downvote
Hi

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

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)
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.

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
 
Upvote 0 Downvote
Sorry, cannot help you.
Maybe somebody smarter than me jumps in and give you some suggestions.

Below, in [red]red[/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 
    [red]ON WHAT FIELDS?[/red]
INNER JOIN dbo.WorksOrderSchedule 
    ON dbo.vwWorksSchedule.WorksOrderScheduleID    = dbo.WorksOrderSchedule.WorksOrderScheduleID 
INNER JOIN dbo.WorksOrderHeader 
    [red]ON WHAT FIELDS?[/red]
INNER JOIN dbo.WorksOrderScheduleLine 
    ON dbo.WorksOrderHeader.WorksOrderID           = dbo.WorksOrderScheduleLine.WorksOrderID 
    [red]ON[/red] dbo.vwWorksSchedule.WorksOrderScheduleID    = dbo.WorksOrderScheduleLine.WorksOrderScheduleID 
   AND dbo.vwWorksSchedule.WOSLineNumber           = dbo.WorksOrderScheduleLine.LineNumber 
   AND dbo.vwWorksSchedule.WOSLineType             = dbo.WorksOrderScheduleLine.LineType 
    [red]ON[/red] dbo.[148-vwWOFinishedProducts].WorksOrderID = dbo.WorksOrderHeader.WorksOrderID 
RIGHT OUTER JOIN dbo.Product 
    [red]ON WHAT FIELDS?[/red]
INNER JOIN dbo.ProductPack 
    ON dbo.Product.ProductID       = dbo.ProductPack.ProductID 
    [red]ON[/red] 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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Niki_S
  • Locked
  • Question
Join SQL tables in foxpro
Replies
1
Views
110
pjw001
pjw001
ms901Boss
  • Locked
  • Question
Join two select statements and collecting data from both
Replies
13
Views
173
mikrom
mikrom
jimoo
  • Locked
  • Question
Unexpected Join Results
Replies
3
Views
137
Andrzejek
Andrzejek
Lidias13
  • Locked
  • Question
Issue trying to find "closest" dated record to a rather complex query (not working)
Replies
1
Views
126
ggriffit
ggriffit
Ikatiemarie
  • Locked
  • Question
Sp_send_dbmail permissions issue
Replies
0
Views
139
Ikatiemarie
Ikatiemarie

Part and Inventory Search

Sponsor

Back
Top