Ted Roche wrote: > On Fri, Aug 6, 2010 at 4:32 PM, Ken Kixmoeller f/h > <[email protected]> wrote: > >> I saw an example posted here (by Ted?) where some of the selection >> criteria were mixed in with the JOIN statements, like this: >> >> ...from ppl inner join loca >> on ppl.loc_id = loca.loc_id >> AND ppl.first = "Esmerelda" >> inner join tran >> on tran.per_id = ppl.per_id >> AND tran.duedate > date() >> inner join product >> on tran.prod_id = product.prod_id >> AND product.pcategory = "Flubber" >> >>
> Bear in mind I'm rarely working with VFP DBFs any more, so you'll need > to test this if you are. Other SQL databases work with this. My own billing system is in VFP, and I get the very occasional old-customer call, wanting some little report or something, but other than that, I am using MySQL and wondering about moving to another SQL engine. > Adding not only the join conditions but also the filtering conditions > in the ON clause effectively gives you sub-tables to work with. You > can even add more joins off them, but warning! There's a lot of sharp > corners there. > > As for when that makes sense, your example above, with all inner > joins, isn't always appropriate. Some SQL optimizers only work > properly with filter clauses in the WHERE clause, so tread carefully > and test your assumptions! I only assumed that I knew how to do it (with all of the "where" stuff in the traditional spot). It was only seeing a posted example with a filter clause mixed up with the JOINs that I wondered if there was a better way that had escaped me. Always looking to move up a notch and learn something new. Thanks, Ken _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
