List:General Discussion« Previous MessageNext Message »
From:Arthur Fuller Date:October 3 2002 7:23pm
Subject:Re: Is there Examples and Documents on joining tables?
View as plain text  
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" <Llappin4@stripped>
To: "mysql" <mysql@stripped>
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 <mysql-thread121245@stripped>
> To unsubscribe, e-mail
<mysql-unsubscribe-artful=rogers.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>

Thread
Is there Examples and Documents on joining tables?damovand3 Oct
  • gemini/acid or innodb ?David yahoo3 Oct
  • Re: Is there Examples and Documents on joining tables?Arthur Fuller3 Oct
  • Re: Is there Examples and Documents on joining tables?Josh Trutwin3 Oct
  • Re: Is there Examples and Documents on joining tables?damovand4 Oct
  • Re: Is there Examples and Documents on joining tables?John Ragan4 Oct
    • Re: Is there Examples and Documents on joining tables?damovand4 Oct
      • Re: Is there Examples and Documents on joining tables?John Ragan4 Oct
      • Re: Is there Examples and Documents on joining tables?John Ragan4 Oct
      • informationFlorian Litot16 Oct
    • Re: informationGustavo Vieira Gonçalves Coelho Rios17 Oct
  • Re: Is there Examples and Documents on joining tables?Arthur Fuller4 Oct
  • Re: Is there Examples and Documents on joining tables?Arthur Fuller4 Oct
Re: Is there Examples and Documents on joining tables?Josh Trutwin4 Oct