Send me create-table scripts and population scripts and I'll do this for you. But on rereading your message I'm guessing that your normalization is wrong. You have more than one multi-valued dependency. Your example description is either wrong or insufficient.
T1 (presumably the parent of all parents) has a PK of let's say A. T2 has an FK into T1 := A. T3 should not have any trace of A or B or C, all of which are derivable. I hope this won't strike you as pedantic, but you get into severe problems when you store multiple multi-valued keys in any given table. This is the difference between 3NF and 4NF+BCNF. Jargon aside, suppose the following: T1 = Customers, with columns CityID, RegionID and CountryID Should you choose this model, you either: a) force the front end to intelligently preclude absurd options; or b) face the prospect of such entries as "Toronto", "New York", "Japan". IMO this is obviously incorrect. The most you need to store is the least you need to store. Instead of this method, consider a cascading alternative: Cities -= Regions -= Counties All you need to store in this model is the CityID, since this is enough to distinguish Springfield, Ohio from Springield, Nebraska. a) you don't need to carry the cascading FKs and b) they are counterproductive even should you choose to carry them, for they either permit nonsense or you force the front end to carry mucho additional logic that is not properly part of the front end. Instead consider intelligent multi-table queries that retrieve for example "Vancouver, BC, Canada" and "Vancouver, WA, USA". This makes your front end smarter and your back end more resilient. Examples of this are abundant. A front end that permits you to transfer employee 123 from department 234 to department 234 are IMO bad, beneath consideration of serious programming. We have to be a lot smarter than that. Given a screen that allows employee-transfer, the least it could do is preclude transfer to the same department! Lest I sound like some proselitizer, I only do so because I have made this mistake a dozen times and paid the price. At this late stage in the game, I now fully appreciate the virtues of BCNF+4NF and hope never to make this mistakes again. Normalize to the max and you're guaranteed to be correct. Codd is no fool. He is a genious and the more you read him the more sense he makes. I wish I were an order of magnitude close to his perceptions. I look back on my errors and realize I should have read him more closely, and obeyed his edicts even if I couldn't comprehend them at the time! ----- Original Message ----- From: "damovand" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: "mysql" <[EMAIL PROTECTED]> Sent: Thursday, October 03, 2002 8:08 PM Subject: Re: Is there Examples and Documents on joining tables? > Hi Arthur, > > Thanks for your answer and your example. Your example is very good but I > can't map to what I'm trying to do. As you suggested I have to > understand how inner join is implemented in MySQL. The query that I'm > working on selects fields from three tables, in this way, > Table_1 has columns A,B,C supplies fields ( A,B, C) > Table_2 has columns A,T,U supplies fields (T,U) > Table_3 has columns A,B,C,X,Y,Z supplies fields (X,Y,Z) > > Table_1 and table_2 have a strange relationship with each other, one row > in table_3 could match with a number of rows in table_1 if table_1.A = > table_3.A and table_1.B=table_3.B and (table_1.C=table_3.C or table_3.C > = "*"). That's what I can't get to work. > > I worked on this on MSSQL and wrote a query that does the above but I > cannot find the equivalent query for it in MySQL. > > For I've split the query in two parts and put the results back together > in my application. But I would look at DBTools and see if I can learn > something from it. > > Thanks again for you advice. > > > [EMAIL PROTECTED] wrote: > > > Once you understand the logic of joins the rest is pretty straightforward, > > since in terms of execution the sequence in which you do the joins is > > irrelevant. (Performance is another issue, quickly resolved by specifying > > the smallest tables first.) > > > > Supposing that you have tables Products, Suppliers and Categories, which > > Products having foreign keys referencing Suppliers and Categories... > > > > SELECT ProductID, ProductName, SupplierID, SupplierName, CategoryID, > > CategoryName > > FROM Products INNER JOIN Suppliers ON Products.SupplierID = > > Suppliers.SupplierID > > INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID; > > > > You can just keep building up such a query according to the number of tables > > you need to drag into the operation. Given the above query, you may well > > want to change the order of table references, but that doesn't intrude upon > > the logic and unless you have a very large number of rows in one or more > > tables, the performance difference will barely be noticeable. > > > > There are several GUI tools that let you perform multi-table selects using > > drag-and-drop techniques (c.f. DBTools, IMO one of the finest, with the > > added benefit that it remembers relatinships that you declare within it). > > Then you can inspect the code it writes and tweak it to suit using EXPLAIN. > > > > hth, > > Arthur > > > > ----- Original Message ----- > > From: "damovand" <[EMAIL PROTECTED]> > > To: "mysql" <[EMAIL PROTECTED]> > > Sent: Thursday, October 03, 2002 12:31 PM > > Subject: Is there Examples and Documents on joining tables? > > > > > > > >>Is there a document that compiles examples on different ways of joining > >>two or more tables? I found a few on > >>http://www.mysql.com/doc/en/JOIN.html but they do not cover joining more > >>than two tables. > >> > >>Thanks for any suggestions. > >> > >> > >>--------------------------------------------------------------------- > >>Before posting, please check: > >> http://www.mysql.com/manual.php (the manual) > >> http://lists.mysql.com/ (the list archive) > >> > >>To request this thread, e-mail <[EMAIL PROTECTED]> > >>To unsubscribe, e-mail > >> > > <[EMAIL PROTECTED]> > > > >>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > >> > >> > > > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
