Multiple case and conditions

June 19, 2024

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?

>Solution :

One option would be using FILTER clause.

Join once for the Title and once for the Author

SELECT  c.collection_id,
        MAX(cdTitle.detail_value) FILTER (WHERE cdTitle.detail_type = 'title') AS title,
        MAX(cdAuth.detail_value) FILTER (WHERE cdAuth.detail_type = 'author') AS author
FROM collections c
LEFT JOIN collection_details cdTitle ON c.collection_id = cdTitle.collection AND cdTitle.detail_type = 'title'
LEFT JOIN collection_details cdAuth ON c.collection_id = cdAuth.collection AND cdAuth.detail_type = 'author'
GROUP BY c.collection_id
ORDER BY c.collection_id asc;

Alternatively you could use a subquery to get the author and book title

SELECT collection_id, book_author,book_title
FROM collections c
LEFT JOIN  (SELECT collection,
                  MAX(CASE WHEN detail_type = 'author' THEN detail_value END) AS book_author ,
                  MAX(CASE WHEN detail_type = 'title' THEN detail_value END) AS book_title 
           FROM  collection_details 
           GROUP BY collection 
         ) cd on cd.collection=c.collection_id
ORDER BY collection_id asc;

See example