Claudio Nanni wrote: > 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 <mailto: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 <http://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 <mailto: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 > > Mille grazie, Claudio... :-)
I understand all that you have mentioned and appreciate your effort. I have read and looked at most of the material about the JOINs, strings, etc. The real problem is the logic (or implementation) of this stuff. e.g., the CONCAT - it is a bit hard to understand how this is integrated with (in this instance) the authors and book_authors tables... in other words, just what is the relation between the "AS Author" and the author and book_author tables? What I don't understand is how to filter (or link/JOIN; meaning give paths to follow to arrive at one row = book, author, publisher) for all books (10 in my case). The above example gives me 100 rows with limited relevance; that is, the books are repeated 10 rows each, one after the other (the first row is correct, but that is only because the author, book and publisher happen to be the first listed in their tables), the next 9 follow alphabetically by title, but the author and publisher are listed the first ten in author and publishers in the order entered in the tables (first through 10). So my real question is how do I show just 10 rows: book, author, publisher (correctly matched)? -- 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=arch...@jab.org
