PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Multiple case and conditions

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?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    JOIN the collection_details twice: demo at db<>fiddle

    select c.collection_id
      ,t.detail_value as title
      ,a.detail_value as author
from collections as c
left join collection_details as t 
  on c.collection_id = t.collection and t.detail_type='title'
left join collection_details as a
  on c.collection_id = a.collection and a.detail_type='author';
    collection_id title author
    1 Book title 1 John Doe
    2 Book title 2 Jane Doe
    3 Book title 3 John Doe

    Use different aliases and specify the detail_type you want as an additional join condition.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search