Thank you gentlemen. Questions &/or comments annotated below: Walter Heck wrote: > 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. > I do want to know, so I'll look more into it. :) > 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. > When I had my flash of enlightenment came when I noticed that in my many concatenations I had used and posted the NATURAL join (which = NATURAL LEFT join); with a little tweaking I got it to work. But I still don't understand if aliasing AS a is any different from aliasing AS ab (or, for that matter, if I substituted a number for the table. I haven't experimented yet, but is there a difference between using a or ab, cd, abc and just plain t1, t2, t3 or is this the same: one two three. It may seem naive to be asking such questions, but they are not readily answered in the tutorials. > 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/ > I have consulted w3shools and do find that they are not clear on a lot of details... I may be picky about the small details but it is they that make the whole possible. > 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 >> >> >> > >
-- 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
