Joining Tables Help

I cannot figure this one out.

I have a SQL Server Database.

I have 4 tables I want data from.

Customer

Invoice

Shipper

Customer Address.

Customer is keyed by CustomerID.

CustomerID is a foreign key in Customer Address, and Invoice

Invoice is keyed by InvoiceID

InvoiceID is a foreign key in Shipper.

Shipper has a field, ShipTo.

Each Customer can have multiple addresses. The key for Customer Address is a composite of CustomerID and AddressNo.

Now...

If I had one table that had CustomerID and ShipTo in the same table, I could just link CustomerID to CustomerID and ShipTo to AddressNo.

However, I don't have a single table with that information.

I have:

[Customer] -> CustomerID -> [Invoice] -> InvoiceID -> [Shipper] -> ShipTo/AddressNo -> [Customer Address]

However... I also have [Invoice] -> CustomerID -> [CustomerAddress], with the intent that I can look up a single address per invoice by a combonation of it's CustomerID and the ShipTo address of the relevant Shipping record.

Elsewhere, I'd make a new table to combine those keys, but I can't do anything with this database, as it's the backend of a system.

Please help?I'm not clear what for you need to use the table Customer_Addresses, you have the shipped_address info in the Shipper table.

The first three tables I would link like this (using left outer join):

Customer.CustomerID ->Invoice.CustomerID
Invoice.InvoiceID -> Shipper.InvoiceID|||The shipped_address info you refer to is an integer.

Moreover, it's part of a composite key. You need to know which CustomerID (integer) and which addressID (also integer) to look up the correct record on the Customer_Address table, so you can get street address, state, zip, names, etc.

The main problem I have is that Shipper only has one part of the composite key (the address ID), and the other tables have the other half (CustomerID).|||Why not to post an example of those fields?
:wave:|||[Customer]
CustomerID: 1 FName: John LName: Doe
[Invoice]
InvoiceID: 7 CustomerID: 1 Amount: 32.93 InvDate: 1/1/2002
[Shipper]
InvoiceID: 7 ShipToNum: 8
[Customer Address]
CustomerID: 1 AddressNum: 8 Street: 502 Mack Lane City: Tulsa State: OK Zip: 53234

(fake data)

Each of these (fake) records all relates to the same customer/invoice/address.|||I don't know what CR version you're using but you can create a command .|||I'm using Crystal Reports 8. How can I create this command to join these tables appropriately?