New Issue is again Join problems, I have had to add the table customer address as I need to list the City it was delivered to. A customer may have a various delivery addresses.
The code below brings back the correct rows and information except the City. It is listing the Customer city for all 6 tows and not the deliveryaddress city. I have changing the joins in a logical manner but can only get to show addresses if the join is incorrect, it gives 18 rows, in this case the customer as 2 other deliveryaddreses so 6 x 3 = 18. Any help guys, thanks, tried posting in my last post but would let me sorry.
The code below brings back the correct rows and information except the City. It is listing the Customer city for all 6 tows and not the deliveryaddress city. I have changing the joins in a logical manner but can only get to show addresses if the join is incorrect, it gives 18 rows, in this case the customer as 2 other deliveryaddreses so 6 x 3 = 18. Any help guys, thanks, tried posting in my last post but would let me sorry.
Code:
SELECT JourneyHeader.JourneyDate, CONVERT(varchar, JourneyHeader.JourneyNumber) AS JourneyNumber, JourneyLine.OrderID,
JourneyHeader.NoOfDrops, JourneyzDropsByProduct.TotalVolume AS m3, dbo.Vehicle.udfVehicleGroup,
dbo.Vehicle.Registration + ' - ' + dbo.Vehicle.Name AS VehicleName, JourneyzDropsByProduct.ProductCode, ProductGroup_1.Name AS [Group],
ProductGroup.Name AS [Sub-group], Customer.CustomerCode, Customer.Name, dbo.ProductPack.PackRef, dbo.CustomerAddress.City
FROM dbo.CustomerAddress LEFT OUTER JOIN
dbo.JourneyLine AS JourneyLine INNER JOIN
dbo.JourneyHeader AS JourneyHeader ON JourneyLine.JourneyID = JourneyHeader.JourneyID INNER JOIN
dbo.JourneyzDropsByProduct AS JourneyzDropsByProduct ON JourneyHeader.JourneyID = JourneyzDropsByProduct.JourneyID AND
JourneyLine.JourneyLineID = JourneyzDropsByProduct.JourneyLineID INNER JOIN
dbo.Product AS Product ON JourneyzDropsByProduct.ProductID = Product.ProductID INNER JOIN
dbo.ProductGroup AS ProductGroup ON Product.ProductGroupID = ProductGroup.ProductGroupID INNER JOIN
dbo.OrderHeader AS OrderHeader ON JourneyLine.OrderID = OrderHeader.OrderID AND JourneyHeader.JourneyID = OrderHeader.JourneyID INNER JOIN
dbo.Customer AS Customer ON OrderHeader.CustomerID = Customer.CustomerID INNER JOIN
dbo.ProductGroup AS ProductGroup_1 ON ProductGroup.ParentID = ProductGroup_1.ProductGroupID INNER JOIN
dbo.Vehicle ON JourneyHeader.VehicleID = dbo.Vehicle.VehicleID LEFT JOIN
dbo.OrderLine ON OrderHeader.OrderID = dbo.OrderLine.OrderID AND Product.ProductID = dbo.OrderLine.ProductID ON
dbo.CustomerAddress.AddressCode = Customer.AddressCode AND dbo.CustomerAddress.CustomerID = OrderHeader.CustomerID LEFT OUTER JOIN
dbo.ProductPack right JOIN
dbo.OrderLineItem ON dbo.ProductPack.PackID = dbo.OrderLineItem.PackID ON dbo.OrderLine.OrderLineID = dbo.OrderLineItem.OrderLineID
WHERE (JourneyHeader.JourneyNumber = 72821) AND (JourneyLine.OrderID = 3339201)
ORDER BY JourneyNumber DESC