Multiple case and conditions

Question

I have two tables like this:

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 get that desired result with only one query?

Answer 1

One option would be using FILTER clause.

SELECT  c.collection_id,
        MAX(cd.detail_value) FILTER (WHERE cd.detail_type = 'title') AS title,
        MAX(cd.detail_value) FILTER (WHERE cd.detail_type = 'author') AS author
FROM collections c
LEFT JOIN collection_details cd ON c.collection_id = cd.collection 
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