SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    You want what? By when?? Milamber's Avatar
    Join Date
    Jan 2001
    Location
    California
    Posts
    342
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    JOINing in the confusion

    Hello,


    I havejust been introduced to "JOIN"s in a mysql statment, and I went and read the article on it, but I so incredibly confused by it still.


    Can some clearly explain this process to me? The article gave me an idea of how it works, and I realize that this could be VERY handy in a project I am currently working on, however, I can't figure out how to really implement this because I don't understand it.


    Anywho, if you could start out with a good explaination, I can ask questions from there. Thanks!
    -Jeff Minard | jrm.cc - Battlefield 2 Stats

  2. #2
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Umm... I'm not sure I can explain it any clearer than my article does... but I'll take a shot. Say you have two tables:
    Code:
    Articles
    +----+----------+----------+
    | ID | Title    | AuthorID |
    +----+----------+----------+
    |  1 | Article1 | 1        |
    |  2 | Article2 | 1        |
    |  3 | Article3 | 2        |
    +----+----------+----------+
    
    Authors
    +----+-------+
    | ID | Name  |
    +----+-------+
    |  1 | Kevin |
    |  2 | Matt  |
    +----+-------+
    So our database contains three articles and two authors. Articles 1 and 2 are written by author 1 (Kevin), and Article 3 is written by author 2 (Matt).

    Now, say we wanted to display a Web page listing the articles and their authors. We'd need elements of both tables. The best way to do that is to create a SELECT statement that joins rows from the two tables involved.

    The most basic join would be the following:

    SELECT * FROM Articles, Authors

    This combines all of the rows from the Articles table with all the rows from the Authors table:
    Code:
    +----+----------+----------+----+-------+
    | ID | Title    | AuthorID | ID | Name  |
    +----+----------+----------+----+-------+
    |  1 | Article1 | 1        |  1 | Kevin |
    |  1 | Article1 | 1        |  2 | Matt  |
    |  2 | Article2 | 1        |  1 | Kevin |
    |  2 | Article2 | 1        |  2 | Matt  |
    |  3 | Article3 | 2        |  1 | Kevin |
    |  3 | Article3 | 2        |  2 | Matt  |
    +----+----------+----------+----+-------+
    Now, this table doesn't make a lot of sense... What we want is to link the articles with their authors. To do this, we limit the rows displayed to those where the AuthorID from the Articles table matches the ID from the Authors table:

    SELECT * FROM Articles, Authors WHERE Articles.AuthorID=Authors.ID
    Code:
    +----+----------+----------+----+-------+
    | ID | Title    | AuthorID | ID | Name  |
    +----+----------+----------+----+-------+
    |  1 | Article1 | 1        |  1 | Kevin |
    |  2 | Article2 | 1        |  1 | Kevin |
    |  3 | Article3 | 2        |  2 | Matt  |
    +----+----------+----------+----+-------+
    There we go! Each article has been listed alongside its author. Now, all we have to do is strip out the information we don't need by selecting only those columns we're interested in:

    SELECT Title, Name FROM Articles, Authors WHERE Articles.AuthorID=Authors.ID
    Code:
    +----------+-------+
    | Title    | Name  |
    +----------+-------+
    | Article1 | Kevin |
    | Article2 | Kevin |
    | Article3 | Matt  |
    +----------+-------+
    Ta da! This result set is a join of our two tables.
    Last edited by Kevin Yank; Jun 23, 2001 at 09:27.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  3. #3
    You want what? By when?? Milamber's Avatar
    Join Date
    Jan 2001
    Location
    California
    Posts
    342
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thanks

    That actually did help me figure it out. Thanks alot.


    I guess I never quite got how to "WHERE" clause worked from the article. :-)



    Thanks!
    -Jeff Minard | jrm.cc - Battlefield 2 Stats

  4. #4
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've read Matt's articles and, quite frankly, they stink. You should fire him!!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •