JOINing in the confusion

[Milamber]

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!

[Kevin Yank]

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:

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:

+----+----------+----------+----+-------+
| 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

+----+----------+----------+----+-------+
| 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

+----------+-------+
| Title    | Name  |
+----------+-------+
| Article1 | Kevin |
| Article2 | Kevin |
| Article3 | Matt  |
+----------+-------+

Ta da! This result set is a join of our two tables.

[Milamber]

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!

[MattR]

I've read Matt's articles and, quite frankly, they stink. You should fire him!!