I have two tables like below:

| collection_id | added_on            | edited_on |
| ------------- | ------------------- | --------- |
| 1             | 2024-06-19 20:16:41 | null      |
| 2             | 2024-06-19 20:17:42 | null      |
| 3             | 2024-06-19 20:17:57 | null      |

and

| detail_id | collection | detail_type | detail_value |
| --------- | ---------- | ----------- | ------------ |
| 1         | 1          | title       | Book title 1 |
| 2         | 1          | author      | John Doe     |
| 3         | 1          | publisher   | Publisher A  |
| 4         | 2          | title       | Book title 2 |
| 5         | 2          | author      | Jane Doe     |
| 6         | 3          | title       | Book title 3 |
| 7         | 3          | author      | John Doe     |
| 8         | 3          | publisher   | Publisher B  |

First table is the main table for storing my book collection’s ID, and the second table is the collection’s details with foreign key ‘collection’ related to the first table.

I wanted to get the title and author for each book, so I tried:

select collection_id, 
(case when detail_type = 'title' then detail_value end) as title, 
(case when detail_type = 'author' then detail_value end) as author
from collections
left join collection_details on collections.collection_id = collection_details.collection
where detail_type = 'title' or detail_type = 'author';

The result is:

| collection_id | title        | author   |
|---------------| ------------ | -------- |
| 1             | Book title 1 | [null]   |
| 1             | [null]       | John Doe |
| 2             | Book title 2 | [null]   |
| 2             | [null]       | Jane Doe |
| 3             | Book title 3 | [null]   |
| 3             | [null]       | John Doe |

What I actually expected is:

| collection_id | title        | author   |
| ------------- | ------------ | -------- |
| 1             | Book title 1 | John Doe |
| 2             | Book title 2 | Jane Doe |
| 3             | Book title 3 | John Doe |

How can I reach the desired result with only one query?