Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2004
    Posts
    126

    Unanswered: 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?:

    Code:
    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

  2. #2
    Join Date
    May 2003
    Posts
    60

    Simple join

    I 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

  3. #3
    Join Date
    Feb 2004
    Posts
    126
    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.

  4. #4
    Join Date
    May 2003
    Posts
    60
    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

  5. #5
    Join Date
    Feb 2004
    Posts
    126
    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?

  6. #6
    Join Date
    May 2003
    Posts
    60
    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

  7. #7
    Join Date
    Feb 2004
    Posts
    126
    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

  8. #8
    Join Date
    May 2003
    Posts
    60
    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

  9. #9
    Join Date
    Feb 2004
    Posts
    126
    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:
    Code:
    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:
    Code:
    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 Spokane
    And 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:
    Code:
    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.

  10. #10
    Join Date
    May 2003
    Posts
    60
    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

  11. #11
    Join Date
    Feb 2004
    Posts
    126
    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:

    Code:
    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:

    Code:
    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.

  12. #12
    Join Date
    May 2003
    Posts
    60
    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.

  13. #13
    Join Date
    Feb 2004
    Posts
    126
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •