Search code examples
mysqldatabaseone-to-many

MySQL - One to Many Query with results in separate columns

I have a table with structure which is similar to the format mentioned below. They look like this

Book

| ID | Title |
| 1  | A     |
| 2  | B     |

Book_Collaborators

| COLLAB_ID |BookID | Type      |
| 1         | 1     | Author    |
| 2         | 1     | Editor    |
| 3         | 1     | Publisher |

Collaborators

| ID | Title |
| -- | ----- |
| 1  | D     |
| 2  | E     |
| 3  | F     |

I need results in the following format where the details such as author, editor and publisher are displayed in different columns.

Just to be noted that some times details might not be present in Book_Collaborators table

| BOOK ID   | Title  | Author | Editor | Publisher |
| --------- | -----  |------  | ------ | --------- | 
| 1         | A      | D      | E      | F         |

I have come up with this query but I don't want to join multiple times to the same table

select book.id as BookId, c.title as Author,c1.title as Editor
from Book book
left join Book_Collaborators bc on (book.id=bc.book_id and bc.type='Author')
left join Collaborators c on (bc.COLLAB_ID=c.id)
left join Book_Collaborators bc1 on (book.id=bc1.book_id and bc1.type='Editor')
left join Collaborators c1 on (bc1.COLLAB_ID=c1.id)
Solution

  • Show all book info either it has related value in book_collaborator table or not. If only related value needed then use INNER JOIN instead of LEFT JOIN. As book id is unique in book table so aggregate function is used at title column for avoiding it to add GROUP BY clause.

    -- MySQL
SELECT b.id BOOK_ID
     , MAX(b.title) Title
     , MAX(CASE WHEN bc.btype = 'Author' THEN c.title END) Author
     , MAX(CASE WHEN bc.btype = 'Editor' THEN c.title END) Editor
     , MAX(CASE WHEN bc.btype = 'Publisher' THEN c.title END) Publisher
FROM Book b
LEFT JOIN Book_Collaborators bc
       ON b.id = bc.book_id
LEFT JOIN Collaborators c
       ON c.id = bc.collab_id
GROUP BY b.id

    Please check from url https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=069dc205f176968d61d462ad3cad7568