Multiple Joined Tables
This is one of those probably really simple things that's just eluding me...we're new to BarTender and of course are under the gun to produce labels. I've attached a JPEG of our current join setup.
Trying to link to a single SQL Server DB and join 4 tables; OrderHeader, OrderLine, CustFile, CustShipTo
I can achieve the header and line join just fine - the trouble is when I get to the CustShipTo 2 tables away from my header information. I'm trying to achieve this:
Header -----> CustFile -----> CustShipTo
Header contains "ship to" code 3 for Customer A for example - I need to return "ship to" information for only Customer A, Code 3. I'm guessing I've messed up my join type for this. I'm hopeful I can simply use the Join Type dialog box without having to write custom SQL.
Right now...I select the proper header via query and I can get ALL #3 codes for all customers that have one - that isn't what I want.
If I'm not clear on my question - let me know - thanks!
-
How are you specifying Customer A?
0 -
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 -
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 -
No, there's nothing even remotely confusing about your tables. That's plain vanilla.
Did you get it working with my suggestion?
0 -
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 NumberI'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 -
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 -
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 -
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 -
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
请先登录再写评论。
评论
9 条评论