List:General Discussion« Previous MessageNext Message »
From:PJ Date:February 22 2009 3:48am
Subject:left joins & concat
View as plain text  
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 --- pj@stripped
   http://www.ptahhotep.com
   http://www.chiccantine.com

Thread
left joins & concatPJ22 Feb
  • Re: left joins & concatClaudio Nanni22 Feb
    • Re: left joins & concatPJ22 Feb
    • Re: left joins & concatPJ22 Feb
      • Re: left joins & concatWalter Heck22 Feb
        • Re: left joins & concatPJ22 Feb