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=arch...@jab.org
