Join Issue 1

[Cpreston]

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.

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)

 

[TamarGranor]

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

 

[Cpreston]

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

 

[Cpreston]

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

 

[Andrzejek]

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.

 

[Cpreston]

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.

 

[Cpreston]

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

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)

 

[Andrzejek]

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 [blue]aliases[/blue] for your fields, like:
[tt]dbo.[148-WOScheduleView].Machine AS [blue]Name[/blue] [/tt]
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 [tt]dbo.[148-WOScheduleView][/tt] may just be [tt]WOSV[/tt]


---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

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

 

[Cpreston]

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.

 

[TamarGranor]

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:

[pre]First Last Age
John Smith 45
John Smith 46
Mary Jones 27
Mary Jones 28
Mary Jones 29[/pre]

If you issue:

[pre]SELECT DISTINCT First, Last FROM People[/pre]

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

[pre]SELECT DISTINCT First, Last, Age FROM People[/pre]

you'll get 5.

Tamar

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Cpreston]

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.

 

[Andrzejek]

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 [blue]123, 456[/blue], and [blue]475[/blue]

Go to your dbo.ProductPack table and do this:
[tt]
Select DISTINCT ProductID, noofpieces
From dbo.ProductPack
Where ProductID IN ([blue]123, 456, 475[/blue])
[/tt]
What do you see? How many records do you get?


---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

Hi Andy

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

Thanks

 

[Andrzejek]

OK, now we are getting somewhere....
So now you know that there is either one or more than just one [tt]noofpieces[/tt] value (record) for any [tt]ProductID[/tt] in [tt]dbo.ProductPack[/tt] 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.

 

[Cpreston]

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.

 

[Andrzejek]

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:

[tt]SELECT DISTINCT
dbo.WorksOrderHeader.udfType, ...[blue],
(SELECT SUM(noofpieces) from dbo.ProductPack Where ScheduleNumber = 23301) As SumOfnoofpieces [/blue]
FROM dbo.WorksOrderSchedule INNER JOIN ...
[/tt]


---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

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.

 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)

 

[Andrzejek]

Just a shot in the dark here...

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, [blue]
SUM(dbo.ProductPack.noofpieces) As SumOfNoOfPieces[/blue]
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[blue]
INNER JOIN dbo.ProductPack ON dbo.Product.ProductID = dbo.ProductPack.ProductID[/blue]
WHERE (dbo.WorksOrderSchedule.ScheduleNumber = 23301) 
AND (dbo.WorksOrderScheduleLine.LineType = 1) 
[blue]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[/blue]

---- Andy

There is a great need for a sarcasm font.