Skip to main content

Search

Search

Multiple Joined Tables

Comments

9 comments

  • Avatar
    Michael Toupin (mtoupin

    How are you specifying Customer A?

    0
  • Avatar
    Legacy Poster

    I hate using GUI tools to write SQL. You spend more time trying to figure out what the GUI is doing then getting where you wanna go! :)

     

    What you have in your snapshot:

     

    Header -> Line

    Header -> CustShipTo -> CustFile

     

    I don't know your tables, but I would presume you need to link the customer key that "should" exist in the order header with your customer file key, and that's also presuming you actually need some data out of the CustFile table. If you're only including it to link to the proper address data, you shouldn't need that at all, just link the cust number from the order header with the cust number in the address table.

    0
  • Avatar
    Legacy Poster

    How are you specifying Customer A?

     

    As you may have guessed...our actual setup involves more tables than I indicated, but the following should shed more light on things.

     

    Customer A information is stored within the OrderHeader table - actually Customer A ID code.

     

    At label printing time we will enter the order number when queried. The OrderHeader table contains the customer code and the link to the CustomerFile works great - I get the information I need as Gorilla stated.

     

    The trouble I'm getting is that OrderHeader contains a code for an order - say "3", that indicates to use the 3rd ship-to address for the customer A code associated with order 1234. So in other words I need to return the ship-to that matches both "3" AND customer A's code.  If I merely hit the ShipTo table with a "3"...I get a return for EVERY customer that has a "3" code - meaning they have at least 3 ship-to addresses.

     

    Gorilla - I hear ya on the GUI tools - I'm only a semi-casual DB guy though. In order to play with stuff I ended up copying SQL from a...hmm...popular "crystal" clear reporting tool where I'm more familiar with dragging and dropping links from tables. My goal was to try and set things "properly" using the GUI tool if possible.

     

    Hopefully I haven't made things more confusing...probably seriously overthinking things. I'd really like to see how this is accomplished via the GUI.

     

    Thanks for the help!

    0
  • Avatar
    Legacy Poster

    No, there's nothing even remotely confusing about your tables. That's plain vanilla.

     

    Did you get it working with my suggestion?

    0
  • Avatar
    Legacy Poster

    Well...in a word...no.  I've got things rolling with some "custom" SQL...but I'd really like to know what I'm having such a mental block over.  I know this stuff is simple-peanuts for those of y'all doing this every week...I'm just not one of those guys! :ph34r:

     

    I've pasted the SQL that works below - still need additions/changes but it gets me going;

    
     SELECT "oeordhdr_sql"."ord_no", "oeordhdr_sql"."cus_no", "arcusfil_sql"."cus_name", "araltadr_sql"."cus_alt_adr_cd", "araltadr_sql"."cus_name", "araltadr_sql"."addr_1", "araltadr_sql"."addr_2", "araltadr_sql"."city", "araltadr_sql"."state", "araltadr_sql"."zip", "arcusfil_sql"."country", "oeordlin_sql"."cus_item_no", "oeordhdr_sql"."oe_po_no", "oeordhdr_sql"."shipping_dt"
     FROM   "Data_01"."dbo"."arcusfil_sql" "arcusfil_sql" INNER JOIN (("Data_01"."dbo"."oeordhdr_sql" "oeordhdr_sql" INNER JOIN "Data_01"."dbo"."oeordlin_sql" "oeordlin_sql" ON "oeordhdr_sql"."ord_no"="oeordlin_sql"."ord_no") INNER JOIN "Data_01"."dbo"."araltadr_sql" "araltadr_sql" ON "oeordhdr_sql"."cus_alt_adr_cd"="araltadr_sql"."cus_alt_adr_cd") ON "arcusfil_sql"."cus_no"="araltadr_sql"."cus_no"
     WHERE  "oeordhdr_sql"."ord_no"=?Order Number
    
    
    

    I've also attached screen shots of the link diagram used to create the SQL above. Superimposed on that is the BarTender Links GUI that I thought would replicate what I wanted - but it hasn't.  I end up with a ton of repeats - one for every customer that has a "3" shipto code.

     

    What the hek am I missing?

    0
  • Avatar
    Legacy Poster

    You need multiple keys linking the addresses and the order header.

     

    The cus_no in your order header must also be linked to the address table.

    0
  • Avatar
    Legacy Poster

    You need multiple keys linking the addresses and the order header.

     

    The cus_no in your order header must also be linked to the address table.

     

     

    I put a 4th line into the Join GUI - linking cus_no from Header on the left and address table on the right....and I get a circular reference error.  Any other ideas?

    0
  • Avatar
    Legacy Poster

    It's just the lame GUI getting in the way. That is what needs to be done, but you will have to use a custom query. Even though I ragged on the GUI, I'm still a bit surprised that it won't allow a multi-key join. I just tried it on our ERP tables and it complained that the same table was listed twice! Unless there is some other way in the GUI to indicate joins that I haven't seen, it just won't allow it. (I usually ignore the GUI editor, so I could have missed something though.)

     

    Here's a cleaned up legible query you can try

     

    SELECT OH.ord_no
    ,OH.cus_no
    ,CU.cus_name
    ,AD.cus_alt_adr_cd
    ,AD.cus_name
    ,AD.addr_1
    ,AD.addr_2
    ,AD.city
    ,AD.state
    ,AD.zip
    ,AD.country
    ,OL.cus_item_no
    ,OH.oe_po_no
    ,OH.shipping_dt
    FROM Data_01.dbo.oeordhdr_sql OH
    INNER JOIN Data_01.dbo.arcusfil_sql CU
       ON OH.cus_no = CU.cus_no
    INNER JOIN Data_01.dbo.araltadr_sql AD
       ON OH.cus_no = AD.cus_no
       AND OH.cus_alt_adr_cd = AD.cus_alt_adr_cd
    INNER JOIN Data_01.dbo.oeordlin_sql OL
       ON OH.ord_no = OL.ord_no
    WHERE OH.ord_no = ?Order Number
    
    0
  • Avatar
    Legacy Poster

    It's just the lame GUI getting in the way. That is what needs to be done, but you will have to use a custom query. Even though I ragged on the GUI, I'm still a bit surprised that it won't allow a multi-key join. I just tried it on our ERP tables and it complained that the same table was listed twice! Unless there is some other way in the GUI to indicate joins that I haven't seen, it just won't allow it. (I usually ignore the GUI editor, so I could have missed something though.)

     

    Here's a cleaned up legible query you can try

     

    SELECT OH.ord_no
    ,OH.cus_no
    ,CU.cus_name
    ,AD.cus_alt_adr_cd
    ,AD.cus_name
    ,AD.addr_1
    ,AD.addr_2
    ,AD.city
    ,AD.state
    ,AD.zip
    ,AD.country
    ,OL.cus_item_no
    ,OH.oe_po_no
    ,OH.shipping_dt
    FROM Data_01.dbo.oeordhdr_sql OH
    INNER JOIN Data_01.dbo.arcusfil_sql CU
       ON OH.cus_no = CU.cus_no
    INNER JOIN Data_01.dbo.araltadr_sql AD
       ON OH.cus_no = AD.cus_no
       AND OH.cus_alt_adr_cd = AD.cus_alt_adr_cd
    INNER JOIN Data_01.dbo.oeordlin_sql OL
       ON OH.ord_no = OL.ord_no
    WHERE OH.ord_no = ?Order Number
    

     

    Thanks Man - was knocking my head thinking I missed something painfully obvious.  I'll give your code a try too.  Now to figure out how to get my printer to use TrueType...

    0

Please sign in to leave a comment.