INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

More join issues

More join issues

(OP)
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.

CODE --> slq

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 

RE: More join issues

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.

RE: More join issues

(OP)
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

RE: More join issues

There has to be something like that, with dbo.CustomerAddress.AddressCode = Customer.AddressCode AND dbo.CustomerAddress.CustomerID = OrderHeader.CustomerID 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.

RE: More join issues

(OP)
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.

RE: More join issues

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close