"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 "the structure is rather simple: book: id = primary key, field1.... field8 author: id = primary key, first_name, last_name, ordinal publishers: id = primary key, publisher book_author: authID = primary key, references author.id bookID = primary key, references book.id book_publisher: bookID = primary key, references book.id publishers_id = primary, key references publishers.id Question 1 is: How to set up a query for a book with 2 authors? There are a number of problems involved, but such issues as more than 2 authors, editors and authors in a dictionary of anthology or some compilation can by revealed in the description field. So we come down to the problem of just plain 2 authors: do we list the same book twice with a different author in each instance with the ordinal field showing 1 for the display priority of the first author and 2 for the second? Question 2 is: How to retrieve and display the two authors on one line as Author_1 "and" Author_2 without some incredible conditional contortions to achieve the goal? -- 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
