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" > > > Questions: > - In what situations does this make sense? (or Not) > - What are the advantages? > > "Anyone? Anyone?" (Knock, knock -- anyone still thinking on Friday > afternoon?) > 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. I'm joining one table several times, but picking up different subsets of records. Made-up example code: Students can get licenses from multiple states. Want to display in separate columns: SELECT Student.*, MA-License.Credits MA-Credits, NH-Licence.Credits NH-Credits, VT-License.Credits VT-Credits, ME-License.Credits ME-Credits FROM Student LEFT OUTER JOIN License AS "MA-License" ON license.student=student.student and license.state = "MA" LEFT OUTER JOIN License AS "NH-License" ON license.student=student.student and license.state = "NH" LEFT OUTER JOIN License AS "VT-License" ON license.student=student.student and license.state = "VT" LEFT OUTER JOIN License AS "ME-License" ON license.student=student.student and license.state = "ME" GROUP BY Student.ID With a net result of one row per student with data summarized across records. Obviously, there'd need to be more than this example, SUM(), MAX() and/or NVL() functions to handle the columns, but it can be a spiffy and powerful cross-tabulation function. 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! -- Ted Roche Ted Roche & Associates, LLC http://www.tedroche.com _______________________________________________ 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.
