The problem arise when there are no data for books in specific library. Consider a following working scenario.
Table name: library
x
--------------------------------
| id | name | owner |
--------------------------------
| 1 | ABC | A |
| 2 | DEF | D |
| 3 | GHI | G |
--------------------------------
Table name: books
--------------------------------
| id | title | library |
--------------------------------
| a | xxx | 1 |
| b | yyy | 1 |
| c | zzz | 2 |
--------------------------------
Now when I do query like below:
SELECT library.name, array_agg(b.title) AS book_list FROM library,
(SELECT title FROM books WHERE books.library = :library_no) as b
WHERE library.id = :library_no GROUP BY library.id
The query generates output for library 1 & 2, but not for library 3. Why and how to solve this issue? (Generate an empty list on no library books)
Required Output:
----------------------
| name | book_list |
----------------------
| GHI | {} | # or {null}
-----------------------
PS: I’ve even tried coalesce as below:
SELECT library.name, coalesce(array_agg(b.title), ARRAY[]::VARCHAR[]) AS book_list FROM library,
(SELECT title FROM books WHERE books.library = :library_no) as b
WHERE library.id = :library_no GROUP BY library.id
Postgres version: 12
Advertisement
Answer
You should use left join for this kind of scenarios like below:
select
l.name,
array_agg(b.title)
from library l left join books b on l.id=b.library
where l.id=3 -- here you can pass you library ID using :library_no variable
group by l.name