Returning incorrect amount of rows 1

[Cpreston]

Hi

I have a View which I have tried to add in a calculation for LineCost. This meant I had to add in the table Delivery as it holds the linecost

After adding the table I now have a result of 80 rows where in fact there should only be 8 rows returned. I have tried to use different Joins but cannot see to get anything but 80 rows. Could someone point advise what to do please. Thanks

SELECT		     dbo.JourneyHeader.JourneyDate, dbo.Vehicle.Name, dbo.JourneyHeader.DriverName, dbo.JourneyHeader.VehicleName, 
                      dbo.JourneyHeader.JourneyNumber, dbo.JourneyHeader.TotalVolume, dbo.JourneyLine.DropNumber, dbo.OrderHeader.DeliveryAddress, 
                      dbo.Customer.Name AS [Customer Name], dbo.Customer.County, dbo.Customer.PostCode, dbo.JourneyHeader.NoOfStops, dbo.OrderHeader.udfSalesOrderOTIF, 
                      dbo.JourneyHeader.NoOfPacks, dbo.JourneyHeader.udfLoader1, dbo.JourneyHeader.udfLoader2, dbo.JourneyHeader.TransportCost, dbo.JourneyHeader.Reference, 
                      dbo.DeliveryArea.Name AS DeliveryArea, 
                      dbo.OrderHeader.TotalVolume * dbo.Delivery.LineCost AS LineCost
FROM                 dbo.JourneyHeader INNER JOIN
                      dbo.JourneyLine ON dbo.JourneyHeader.JourneyID = dbo.JourneyLine.JourneyID INNER JOIN
                      dbo.Vehicle ON dbo.JourneyHeader.VehicleID = dbo.Vehicle.VehicleID INNER JOIN
                      dbo.OrderHeader ON dbo.JourneyLine.OrderID = dbo.OrderHeader.OrderID INNER JOIN
                      dbo.Customer ON dbo.OrderHeader.CustomerID = dbo.Customer.CustomerID INNER JOIN
                      dbo.Delivery ON dbo.OrderHeader.DeliveryAreaID = dbo.Delivery.DeliveryAreaID INNER JOIN
                      dbo.DeliveryArea ON dbo.DeliveryArea.DeliveryAreaID = dbo.Delivery.DeliveryAreaID
WHERE     (dbo.JourneyHeader.JourneyNumber = 88668)
ORDER BY dbo.JourneyHeader.JourneyNumber

 

[gmmastros]

Usually, when you get more rows than expected, it means that there is a missing join conditions. For example, it's possible that your delivery table joins to order header on multiple columns instead of just one.

Sometimes it helps to temporarily change the select clause to return all columns from all tables to get a better idea of what's going on.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Cpreston]

When I add it into the view the only key it as a relationship with is the DeliveryArea table. The deliveryarea table is then linked to other tables.

I don't quite understand how you mean to change the clause to show all. Thanks

 

[fredericofonseca]

code formatted and best practices implemented for clarity

select JH.JourneyDate
     , Ve.Name
     , JH.DriverName
     , JH.VehicleName
     , JH.JourneyNumber
     , JH.TotalVolume
     , JL.DropNumber
     , OH.DeliveryAddress
     , Cu.Name as [customer name]
     , Cu.County
     , Cu.PostCode
     , JH.NoOfStops
     , OH.udfSalesOrderOTIF
     , JH.NoOfPacks
     , JH.udfLoader1
     , JH.udfLoader2
     , JH.TransportCost
     , JH.Reference
     , DA.Name as deliveryarea
     , OH.TotalVolume * DE.linecost as linecost
from dbo.JourneyHeader JH
inner join dbo.JourneyLine JL
    on JH.JourneyID = JL.JourneyID
inner join dbo.Vehicle VE
    on JH.VehicleID = Ve.VehicleID
inner join dbo.OrderHeader OH
    on JL.OrderID = OH.OrderID
inner join dbo.Customer CU
    on OH.CustomerID = Cu.CustomerID
inner join dbo.Delivery DE
    on OH.DeliveryAreaID = DE.DeliveryAreaID -- [highlight #CC0000]wonder if this one is correct - probably there is a columns OH.DeliveryID on the table[/highlight]
inner join dbo.deliveryarea DA
    on DA.DeliveryAreaID = DE.DeliveryAreaID
where (JH.JourneyNumber = 88668)
order by JH.JourneyNumber

in order to determine which join is incorrect start with the following
comment out each join from the bottom up - once you get the correct number of rows you know which one is wrong and requires further investigation on how to process its data

select JH.*
     , JL.*
     , VE.*
     , OH.*
     , CU.*
     , DE.*
     , DA.*
from dbo.JourneyHeader JH
inner join dbo.JourneyLine JL
    on JH.JourneyID = JL.JourneyID
inner join dbo.Vehicle VE
    on JH.VehicleID = Ve.VehicleID
inner join dbo.OrderHeader OH
    on JL.OrderID = OH.OrderID
inner join dbo.Customer CU
    on OH.CustomerID = Cu.CustomerID
inner join dbo.Delivery DE
    on OH.DeliveryAreaID = DE.DeliveryAreaID -- wonder if this one is correct - probably there is a columns OH.DeliveryID on the table
inner join dbo.deliveryarea DA
    on DA.DeliveryAreaID = DE.DeliveryAreaID
where (JH.JourneyNumber = 88668)
order by JH.JourneyNumber

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

Hi

There is not a deliveryid field available in OH unfortunately.

I then tried to eliminate what is causing it, but I could not take out the inner join dbo.Delivery DE as it is requiried the DE.DeliveryAreaID in the inner join dbo.deliveryarea DA, hope that made ssnese

inner join dbo.Delivery DE
on OH.DeliveryAreaID = DE.DeliveryAreaID -- wonder if this one is correct - probably there is a columns OH.DeliveryID on the table
inner join dbo.deliveryarea DA
on DA.DeliveryAreaID = DE.DeliveryAreaID

What I know from using the view is as soon as I added in the Delivery table to get the linecost field is when it goes wrong from 8 to 80 rows.

Any ideas please and thanks for the brilliant reply thus far

 

[Cpreston]

Hi

To return the 8 rows expected I have the following code with -- at the front of the code. But this takes out the OH.TotalVolume * DE.linecost as linecost which is a required field.

select JH.JourneyDate
, Ve.Name
, JH.DriverName
, JH.VehicleName
, JH.JourneyNumber
, JH.TotalVolume
, JL.DropNumber
, OH.DeliveryAddress
, Cu.Name as [customer name]
, Cu.County
, Cu.PostCode
, JH.NoOfStops
, OH.udfSalesOrderOTIF
, JH.NoOfPacks
, JH.udfLoader1
, JH.udfLoader2
, JH.TransportCost
, JH.Reference
--, DA.Name as deliveryarea
--, OH.TotalVolume * DE.linecost as linecost
from dbo.JourneyHeader JH
inner join dbo.JourneyLine JL
on JH.JourneyID = JL.JourneyID
inner join dbo.Vehicle VE
on JH.VehicleID = Ve.VehicleID
inner join dbrderHeader OH
on JL.OrderID = OH.OrderID
inner join dbo.Customer CU
on OH.CustomerID = Cu.CustomerID
--inner join dbo.Delivery DE
--on OH.DeliveryAreaID = DE.DeliveryAreaID -- wonder if this one is correct - probably there is a columns OH.DeliveryID on the table
--inner join dbo.deliveryarea DA
--on DA.DeliveryAreaID = DE.DeliveryAreaID
where (JH.JourneyNumber = 88668)
order by JH.JourneyNumber

 

[fredericofonseca]

nothing we can do to help here - normally a order can only have a single delivery id - if deliveryarea is not unique (which I would not think it would be) then OrderHeader or one of the related tables should contain a further field that links into the delivery table and filters down to the exact record to use.

if you supply us with all tables definitions, including foreign keys we may be able to spot a name and/or a relation that points to the correct join - but with the information given there is little we can do.

you may be missing another table - look at this from this perspective

A Journey has a vehicle -- this should be unique if we assume a Journey is a single travel from point A to point B
In order to maximize resources a Journey makes multiple stops along the way - one stop per order for the same customer for the same address - multiple clients may have the same address and have multiple orders delivered on same day.

so..
Journey
- Stop A -- this is the address where location is in same place - could be block of apartments but different doors - or same street different houses
- Order A - Client A
- Order B - Client A
- Order C - Client B
- Stop B
- Order E - Client xx
- Order F - Client zz
- Order G - Client yy
....
End Journey -- all orders delivered (or attempted to deliver)

A possible lineage could be
Order --> client --> address --> delivery
Delivery --> DeliveryArea
--> Vehicle --> Driver
--> Journey --> JourneyLine


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

Thank you for the replies, it pointed me in the right direction. The VehicleTypeID proved to be the link required and I ma now getting the right results. Thanks for the excellent replies and advice appreciated.

inner join dbo.Delivery DE
on OH.DeliveryAreaID = DE.DeliveryAreaID -- wonder if this one is correct - probably there is a columns OH.DeliveryID on the table
inner join dbo.deliveryarea DA
on DA.DeliveryAreaID = DE.DeliveryAreaID AND VE.VehicleTypeID = DE.VehicleTypeID
where (JH.JourneyNumber = 88668)
order by JH.JourneyNumber