More join issues

[Cpreston]

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.

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

 

[Olaf Doschke]

dbo.CustomerAddress.City is likely often both bill address and delivery address, but don't you have a separate table for each delivery choice? I'd put this into order head data.

We can't tell you how your data is structured, you have to know this yourself.

Bye, Olaf.

 

[Cpreston]

Hi

The structure is that in the customer table is the main address for invoice and can also be a delivery address.
Any other delivery addresses are listed in the customeraddress table where the city field I am trying to display is. However, I keep picking up the City filed from the customer table.

Maybe there is a link on the orderheader table for customeraddressID or something like that, I will check this out tomoorow.

Thanks

 

[Olaf Doschke]

There has to be something like that, with [tt]dbo.CustomerAddress.AddressCode = Customer.AddressCode AND dbo.CustomerAddress.CustomerID = OrderHeader.CustomerID[/tt] you may join only the one CustomerAddress record with the main address of the customer. Not, what you want. You have to know the right join condition to dbo.CustomerAddress with some foreign key of the order delivery address.

Bye, Olaf.

 

[Cpreston]

Hi

Yes it was that, I linked the orderheader.customeradreesid to customeraddress.customeraddressid and just the orderheader.customerid to customer.customerid

Then the data came in correct. Thank you for pointing me in the right direction and your help.

 

[Ringedep]

Wow, very great, I will write it in my library.