The 'AS' keyword for tables is just to give a table an alias by which you can then use it in the rest of the query. In your case, when you say 'book as b' in your query, it means that you can use b in places where you need to refer to the book table. eg. 'b.id' refers to the 'id' field of the 'book' table. It is good practice to always use an alias for a table and refer to the table by that alias anywhere in the query. It makes your query easier to read and understand. As a side-effect (which you should probably not focus on just yet ;) ) it means you can use this construction to join a table on itself and use aliasses in the rest of the query to uniquely identify the table you are referring to.
The same idea goes for the as keyword on field names. You can use those aliasses to refer to the column in the gorup by, order by and having clauses of your query. It is mostly used to display a proper column name though. In your example, the concat_ws expresion has an alias so in the queries result this name will be used instead of the expression used to select. A propos: The problem with your first query was that you were using natural joins. Natural joins match up all columns that have the same name in both tables automatically. I consider it bad practice to use it as it might have unexpected results, especially when you alter tables later on. I recommend you to read up on your SQL knowledge though, as these are very basic problems and it is imperative to have a good understanding of SQL to be able to work with (m)any relational databases. I haven't looked at their specific SQL course, but w3schools generally has very good basic introductions: http://www.w3schools.com/sql/ Have fun! Walter OlinData: Professional services for MySQL Support * Consulting * Administration http://www.olindata.com On Sun, Feb 22, 2009 at 4:15 PM, PJ <af.gour...@videotron.ca> wrote: > Gentlemen, > and all those who care, > > THE PROBLEM SEEMS TO BE SOLVED (for now, NEXT is HOW TO QUERY(SELECT) > MULTIPLE AUTHORS AND DISPLAY THAT and then HOW TO DEAL WITH MULTIPLE > CATEGORIES (heh... heh... heh ;-) : > > "SELECT b.title, b.sub_title, b.descr, b.comment, b.bk_cover, > b.copyright, b.ISBN, c.publisher, > CONCAT_WS(' ', first_name, last_name) AS Author > FROM book AS b > LEFT JOIN book_author AS ab ON b.id = ab.bookID > LEFT JOIN author AS a ON ab.authID=a.id > LEFT JOIN book_publisher AS abc ON b.id = abc.bookID > LEFT JOIN publishers AS c ON abc.publishers_id = c.id > ORDER BY title ASC " > > This works.... except I don't fully understand how the CONCAT AS Author > works, nor the AS ab and AS abc. > Does the order of ab and abc matter? Are they related... I think I got > this working by pure experimentation in trying all possible > combinations... kind of primitive, but it seems to work... long hours > and loss of sleep... etc... > > And then there is the warning message at the bottom of the displayed web > page: > > *Warning*: Unknown: Your script possibly relies on a session side-effect > which existed until PHP 4.2.3. Please be advised that the session > extension does not consider global variables as a source of data, unless > register_globals is enabled. You can disable this functionality and this > warning by setting session.bug_compat_42 or session.bug_compat_warn to > off, respectively. in *Unknown* on line *0 > > *I guess I'll have to research it on the web... :-)* > > > * > 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 >> >> > > > -- > > 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=li...@olindata.com > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
