How to create this very complex MySQL query

Question

I have a serious troubles with creating the proper query for the following:

• I have 3 tables.

1. authors ( author_id(int), author_name(varchar) ),

2. books ( book_id(int), book_title(varchar) )

3. contribution ( book_id(int), author_id(int), prec(double)) -- for storing the precentage how deeply an author is involved in creation of a specific book. (so one book may have more then one author)

And the legendary difficulty (for me right now) query has to ask database for book_id, book_title and in a third column all authors of the specified book concatenated with comma and ordered by perecentage of participation. So I have as many rows in the result of the query as many books I have in the books table, and to every book I have to get the title, and authors in a third column. But how can be such mysql query forged?

Answer 1

Little vague on the 3rd table (name) and the join criteria but this should be close...

The key here is the function group_Concat() which allows you to combine multiple rows into one based on the group by values. Additionally the group_Concat function allows you to define a deliminator as well as an order by.

SELECT B.Book_ID
     , B.Book_Title
     , group_concat(A.Author_name order by ABP.Prec Desc separator ', ') as Authors
FROM Author_Book_Percent ABP
INNER JOIN AUTHORS A
  on ABP.Author_ID = A.Author_ID
INNER JOIN BOOKS B
  on ABP.Book_ID = B.Book_ID
GROUP BY B.Book_ID, B.Book_Title