Hi Phil,
you seem quite a bit confused!
I would not step to use joins before understanding the 'simple' logic
behind,
otherwise you will be always confused by the syntax.
There are many, many, many resources (thanks to Tim!)
I will try to give you a simple overview of joins but please get more
confortable with them!
In relational databases the information is(should!) logically separated into
entities which are no more no less represented by tables,
yes the tables you well know(mathematically speaking they are known as
[relations] the R in RDBMS).
So in some application,like yours, you could have the need to deal with
[books], [authors], [publishers].
These, as said, are the so called entities when we speak from an
abstract-logical point of view,
but eventually turn to be the tables in your database.
So you have a table with all the books, a table with all the authors, and a
table with the publishers.
How could we relate each book with his author?
One way is to have extra information on each book (1 book=1 record/row in
the [books] table),
if an author wrote 100 books you would have 100 times the same information
on each of his books.
another way(better!) is to add the extra information as well, but just an
identifier of the author,
an handle, a key, a UNIQUE value (Social Security Number?) , so that you
have only one place
with the author information (the [author] table) which is also great for
maintenance!
Imagine updating an information about the author on 100 rows of the [books]
table,
and update the same information just in one row of the [authors] table.
I think you can imagine also that the UNIQUE value you add to each book
which identifies the book author,
will be present in the [authors] table to be able to identify the author.
Until now we are just speaking about logic and you could do an excercise
with pen and paper,
drawing a line from the AUTHOR UNIQUE ID from the [books] table to the
AUTHOR UNIQUE ID from the [authors] table.
So you could easily find the author of each book by following the line the
links the two rows/records,
on the left you have the books and on the right you have the authors.
Reading from left to right, for instance, you would be able now to read
consequently the book title and the book author name.
Sit back, the JOIN is the line you have just draw.
It is the connection between two tables to be able to have on just one row
all the information that are split into two(or more) parts/table.
The ON clause that you find in the JOIN syntax is the place where you
specify
ON [books].AUTHOR UNIQUE ID = [authors].AUTHOR UNIQUE ID
( by the way the ID that points to the table with all the informations is
also knows as FOREIGN KEY, in this case the left operand)
The resulting table is a table that have each row like a concatenation of
two rows related from the two different tables.
The WHERE clause is used to FILTER, not to connect the two tables!!
After you connect(join) the two tables you could want to see only certain
rows, ok now you use the WHERE.
Forget about CONCAT/CONCAT_WS this is a string function, and is not related
to JOINS.
Please, let me know if this was useful to you.
Claudio Nanni
2009/2/22 PJ <af.gour...@videotron.ca>
> I have been searching and searching for a clear and logical explanation
> of JOINs and have found nothing that can be reasonably understood.
> Perhaps I am dense or from another planet, but nothing seems to fall
> into place.
> I need to display all the books (with their respective authors and
> publishers) -
> the tables are book, author, publisher and book_author, book_publisher
> for linking many to many books-authors and books-publishers.
>
> Here is what I have (and it gives me rather confusing results:
>
> "SELECT b.title, b.sub_title, b.descr, b.comment, b.bk_cover,
> b.copyright, b.ISBN, b.sellers, c.publisher,
> CONCAT_WS(' ', first_name, last_name) AS Author
> FROM book AS b, publishers AS c
> NATURAL JOIN book_author AS ab
> NATURAL JOIN author AS a
> NATURAL JOIN book_publisher AS d
> WHERE d.bookID = b.id
> ORDER BY title ASC "
>
> First, I see what CONCAT_WS does (more or less) but I cannot grasp the
> logic related to the author and book_author tables.
>
> Second, I don't understand the logic of the linking tables since the
> relationships seem to have to be stated outside the tables... (i.e. the
> foreign key reference is in the table but seems to be necessary in a
> WHERE clause as well.... ???
>
> And lastly, I don't understand what conditions (ON or WHERE clauses)
> need to be included to get the books matched up to their respective
> authors and publishers.
>
> One link that I have been trying to fathom is
>
> http://www.java2s.com/Tutorial/MySQL/0100__Table-Join/Catalog0100__Table-Join.htm
> but it is not very helpful in any explanations...
>
> I sure would like to hear some clear explanations...
> TIA
>
> --
>
> Phil Jourdan --- p...@ptahhotep.com
> http://www.ptahhotep.com
> http://www.chiccantine.com
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
>
>
