Yep. This was the kind of thing I was looking for. Didn't know you could do "ON shipping.toloc = ....." Thanks for your help all. Appreciate the feedback!
-----Original Message----- From: William Conlon [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 05, 2007 12:12 PM To: [email protected] Subject: Re: Witango-Talk: Question of joins Gene, You're after something like this? SELECT shipping.id, shipping.FromLoc, shipping.ToLoc, To_location.name, From_location.name FROM shipping LEFT JOIN locations To_location ON shipping.ToLoc = To_location.id LEFT JOIN locations From_location ON shipping.FromLoc = From_location.id WHERE ... On Jun 5, 2007, at 9:03 AM, Beverly Voth wrote: > Gene, if these are in an array, then you have to look them up, eh? :D > If they are in a SQL table, you have to query them, right? > > You have a "join table". If you are creating these "on-the-fly" and > putting the values into an array, why not put the "text" locations > into columns, too, as you make the "join table/array"? > > -- > Beverly Voth Tier3 Data & Web Services Group, LLC > 606-864-0041 http://www.tier3web.com/xml.htm > > Certified FileMaker 7 Developer * FileMaker Business Alliance > Web Design & Hosting > Coldfusion, Witango, PHP, MS SQL, MySQL, FMP, XML/XSLT, CSS > Over 12 years experience integrating databases and the internet! > > > On 6/5/07 11:44 AM, "Wolf, Gene" <[EMAIL PROTECTED]> wrote > in whole > or in part: > >> I knew this wasn't going to be clear the first time. *laughs* >> >> Assume a record with the following fields and data: >> >> Field names: RecID FromLoc ToLoc >> 1 10 12 >> 2 14 10 >> 3 11 9 >> 4 12 11 >> >> >> Now, the from and to locations are simply keys to another table >> containing the description. My question is, what is the easiest >> way to >> display the text value of the from and to location values other than >> iterating through an array and looking up each individual value? >> >> ________________________________ >> >> From: Jesse Parker [mailto:[EMAIL PROTECTED] >> Sent: Tuesday, June 05, 2007 11:32 AM >> To: [email protected] >> Subject: RE: Witango-Talk: Question of joins >> >> >> I'm not sure I'm following you. I think you have a file that >> contains >> an "array" of values, and you want to do a join-like operation >> with your >> DB for reporting purposes. >> >> If this is the case, you can probably use <@FILTER> to get only the >> relevant records from each array. If you post more detail, I will >> try >> to give you the filter expression. >> >> I think you might be happier in the long run if you put your list of >> values in the DB, instead of using the file to store these values >> - that >> way you can use a simple join, and it's likely to be much faster than >> any file-based operations. >> >> >> -----Original Message----- >> From: Wolf, Gene [mailto:[EMAIL PROTECTED] >> Sent: Tuesday, June 05, 2007 11:00 AM >> To: [email protected] >> Subject: Witango-Talk: Question of joins >> >> >> >> This is probably simplistic and perhaps more of a SQL >> question than a Witango question but I'm going to ask anyway. >> *laughs* I >> have an application set up where a user selects a from location >> and a to >> location, from a dropdown list, of something they are shipping. The >> dropdown list is populated from a file and the resulting from and to >> location codes are recorded to a database record in separate fields >> named shipfrom and shipto. No problem so far. Everything works >> perfectly. >> >> Now, in reporting I want to show the description of the ship >> from and ship to locations. I can read the records into an array and >> look up each location from the database and then display the array >> when >> done but that seems inefficient, especially when I have to use a for >> loop to process the array. Is there some way to join two different >> fields on the same lookup table, in a Witango search action, in >> order to >> pull different text values for each of the fields? >> >> Gene Wolf > > ______________________________________________________________________ > __ > TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf > ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
