Be careful of using "=*" and "*=", some of our SQL servers will take that and others require the "... OUTER JOIN" syntax.
-- Beverly Voth Tier3 Data & Web Services Group, LLC 606-864-0041 http://www.tier3web.com/xml.htm 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 2:34 PM, "Ben Johansen" <[EMAIL PROTECTED]> wrote in whole or in part: > You can do this in the search action builder > by selecting the > > =* (left outer join) > *= (right outer join) > > in the join section > > Ben > > On Jun 5, 2007, at 9:34 AM, Wolf, Gene wrote: > > 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: witango-talk@witango.com > 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: witango-talk@witango.com >>> 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: witango-talk@witango.com >>> 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
