Help with simple join?

I don't know if it's Friday or what, but I can't for the life of me come up with an easy way to do this:

I have 3 tables I want to join:

Sale Table:
Sale_No Cus_No Sale_Qty
1 Joe01 250

Order Table:
Ord_No Sale_No Order_Qty ShipToCode
1 1 20 DestA
2 1 20 DestA
3 1 20 DestA
4 1 20 DestB
5 1 20 DestB

ShipTo Table:

Cus_No ShipToCode ShipToName
Joe01 DestA Philadelphia
Joe01 DestB Chicago
Bob01 DestA Boston

A sale for say 100 tons would have 5 orders (each for 20 tons) associated with it by Sale_No. Each of those orders can go to a different ShipTo destination. Since only the ShipTo Code is stored in the Orders table, I need to get the ShipToName. However, As demonstrated in the example table above, the key in the ShipTo table is both Cus_No AND ShipToCode.

I want a list of Sales and Orders, which is an inner join on Sale_No, piece of cake. However, I then need to use the ShipTo table to go from the ShipToCode to the ShipToName. Unfortunately, Cus_No is not in the Orders table, it is back in the Sales table (proper normalization is a pain sometimes).

What I came up with is this, but is this correct?:

FROM Sales INNER JOIN
Orders ON Sales.sale_no = Orders.sale_no INNER JOIN
ShipTo ON Orders.ShipToCode = ShipTo.ShipToCode AND
Sales.cus_no = ShipTo.cus_noI built and populated the tables and used this query:
SELECT Sale.Sale_No, Sale.Cus_No, Sale.Sale_Qty, [Order].Order_Qty, ShipTo.ShipToName
FROM Sale INNER JOIN
[Order] ON Sale.Sale_No = [Order].Sale_No INNER JOIN
ShipTo ON [Order].ShiptoCode = ShipTo.ShipToCode

It returned this result:

Sale_No Cus_No Sale_Qty Order_Qty ShipToName
1 Joe01 250 20 Philadelphia
1 Joe01 250 20 Philadelphia
1 Joe01 250 20 Philadelphia
1 Joe01 250 20 Chicago
1 Joe01 250 20 Chicago

Not sure if this is what you want?

best regards
mkal|||Hmmm, did you include the last row of the ShipTo Table:
Bob01 DestA Boston

Wouldn't your query bring up a row for orders 1, 2, and 3 for both Boston and Philadelphia?

My issue is that I need to get to the ShipToName in the ShipTo table from the ShipToCode in the Orders table, BUT I need to include Cus_No in the join because the PK in the ShipTo table is both ShipToCode AND Cus_No.

Thank you for your help.|||It would if Bob01 was in the Sales table but he is not. Sales joins to Orders on the Sale_No, no Sale no bob

hope this helps
mkal|||How does it know that?

You're just joining on ShipToCode, and for orders 1,2, and 3, the ShipToCode is 'DestA' and for Boston and Philadelphia, the ShipToCode is 'DestA'.

Where in your join does it know to match on cus_no also?|||In the Sales table we have Joe01 and in the orders table we have Joe01(numerous times) so the first join between Sales and Orders (on the Sales_No) returns 5 rows, these five rows are then in turn joined to the ShipTo table, but since bob01 is not in the Sales or Orders table the first join returns no rows, with no row returned there is nothing to join on to the ShipTo table.

hope this helps
mkal|||Cus_no isn't in the order's table, that's my problem, so Joe01 isn't in the orders table, only in the Sales table. I still am missing any connection by cus_no between the orders and the ShipTo table.

so for example, if the sample data were:

Sale Table:
Sale_No Cus_No Sale_Qty
1 Joe01 250
2 Bob01 250

Order Table:
Ord_No Sale_No Order_Qty ShipToCode
1 1 20 DestA
2 1 20 DestA
3 1 20 DestA
4 1 20 DestB
5 1 20 DestB
6 1 20 DestA
7 1 20 DestA
8 1 20 DestA
9 1 20 DestB
10 1 20 DestB

ShipTo Table:

Cus_No ShipToCode ShipToName
Joe01 DestA Philadelphia
Joe01 DestB Chicago
Bob01 DestA Boston
Bob01 DestB Spokane

how does the join know to differentiate between DestA = Philadelphia for Joe01 and DestA = Boston for Bob01 when there are no joins on Cus_No?

...I don't think i'm missing anything|||Your problem lies in the data in the Orders table it should look like this
Ord_No Sale_No Order_Qty ShiptoCode
1 1 20 DestA
2 1 20 DestA
3 1 20 DestA
6 2 20 DestA
7 2 20 DestA
8 2 20 DestA
9 2 20 DestB
10 2 20 DestB
4 1 20 DestB
5 1 20 DestB

Then the query returns
Sale_No Cus_No Sale_Qty Order_Qty ShipToName
1 Joe01 250 20 Philadelphia
1 Joe01 250 20 Philadelphia
1 Joe01 250 20 Philadelphia
2 Bob01 250 20 Philadelphia
2 Bob01 250 20 Philadelphia
2 Bob01 250 20 Philadelphia
2 Bob01 250 20 Chicago
2 Bob01 250 20 Chicago
1 Joe01 250 20 Chicago
1 Joe01 250 20 Chicago|||Sorry about that, you are correct about the Orders Table, I forgot to change the Sale_No when I added the addtional orders.

Assuming this data:
Sale Table (I changed the Qty just to differentiate):
Sale_No Cus_No Sale_Qty
1 Joe01 250
2 Bob01 100

Order Table(I changed the Qty just to differentiate):
Ord_No Sale_No Order_Qty ShipToCode
1 1 50 DestA
2 1 50 DestA
3 1 50 DestA
4 1 50 DestB
5 1 50 DestB
6 2 20 DestA
7 2 20 DestA
8 2 20 DestA
9 2 20 DestB
10 2 20 DestB

ShipTo Table:
Cus_No ShipToCode ShipToName
Joe01 DestA Philadelphia
Joe01 DestB Chicago
Bob01 DestA Boston
Bob01 DestB Spokane

I want this returned:
Sale_No Cus_No Sale_Qty Order_Qty ShipToName
1 Joe01 250 50 Philadelphia
1 Joe01 250 50 Philadelphia
1 Joe01 250 50 Philadelphia
1 Joe01 250 50 Chicago
1 Joe01 250 50 Chicago
2 Bob01 100 20 Boston
2 Bob01 100 20 Boston
2 Bob01 100 20 Spokane
2 Bob01 100 20 Spokane
2 Bob01 100 20 SpokaneAnd unless I am mistaken, the query that you used does not distinguish between the ShipToNames for Bob01 and Joe01 because it doesn't join on cus_no.

But I think this one does:

FROM Sales INNER JOIN
Orders ON Sales.sale_no = Orders.sale_no INNER JOIN
ShipTo ON Orders.ShipToCode = ShipTo.ShipToCode AND
Sales.cus_no = ShipTo.cus_no
I'm just curious if you can even do what I'm doign here, which is joining on fields that aren't in the two tables being joined, I think that makes sense.

Thanks for your help.|||So here's my query:
SELECT Sale.Sale_No, Sale.Cus_No, Sale.Sale_Qty, [Order].Order_Qty, ShipTo.ShipToName
FROM ShipTo INNER JOIN
Sale ON ShipTo.Cus_No = Sale.Cus_No INNER JOIN
[Order] ON Sale.Sale_No = [Order].Sale_No

and here are the results:
Sale_No Cus_No Sale_Qty Order_Qty ShipToName
1 Joe01 250 50 Philadelphia
1 Joe01 250 50 Chicago
1 Joe01 250 50 Philadelphia
1 Joe01 250 50 Chicago
1 Joe01 250 50 Philadelphia
1 Joe01 250 50 Chicago
2 Bob01 100 20 Boston
2 Bob01 100 20 Boston
2 Bob01 100 20 Boston
2 Bob01 100 20 Boston
2 Bob01 100 20 Boston
1 Joe01 250 50 Philadelphia
1 Joe01 250 50 Chicago
1 Joe01 250 50 Philadelphia
1 Joe01 250 50 Chicago

I may be mistaken but I think this is what you want.
best regards.
mkal|||No, what I am looking for is really just the orders table and then I'm going to the other tables to lookup names from the codes. Maybe this will help:

Ord_No Sale_No Cus_No Sale_Qty Order_Qty ShipToCode ShipToName
1 1 Joe01 250 50 DestA Philadelphia
2 1 Joe01 250 50 DestA Philadelphia
3 1 Joe01 250 50 DestA Philadelphia
4 1 Joe01 250 50 DestB Chicago
5 1 Joe01 250 50 DestB Chicago
6 2 Bob01 100 20 DestA Boston
7 2 Bob01 100 20 DestA Boston
8 2 Bob01 100 20 DestA Boston
9 2 Bob01 100 20 DestB Spokane
10 2 Bob01 100 20 DestB Spokane

which I think I get from:

FROM Sales INNER JOIN
Orders ON Sales.sale_no = Orders.sale_no INNER JOIN
ShipTo ON Orders.ShipToCode = ShipTo.ShipToCode AND
Sales.cus_no = ShipTo.cus_no

I'm just wondering if what I'm getting is coincidental or if I can legitimately use tables in the ON clause that aren't in that line of the JOIN.|||I guess now I'm the one who is confused. Why would you use values from a single query on the Orders table to be used in a lookup query. The select statement that joins the three tables together gives you all of what you're looking for. If you want to see the value of the ShipToCode & Ord_No (yours has it mine didn't) just add it to the select statement like below.

SELECT [Order].Ord_No, Sale.Sale_No, Sale.Cus_No, Sale.Sale_Qty, [Order].Order_Qty, [Order].ShipToCode, ShipTo.ShipToName
FROM ShipTo INNER JOIN
Sale ON ShipTo.Cus_No = Sale.Cus_No INNER JOIN
[Order] ON Sale.Sale_No = [Order].Sale_No

If for some reason you need to lookup values in the Sales & ShipTo tables based on what is in the Orders table then you will probably need to use a cursor.

And as far as the data your returning, I think its legitimate, meaning it will return the same values each and everytime the query is run.|||I only added the Ord_No and ShipToCode to the query output to show which rows were generating which values.

Anyway...I think we've beaten this to death, this has been helpful in examining the table structure and query design, but I think we can both move on with our productive lives now.

Thank you. This place is great.