I'm trying to find a solution in PostgreSQL of how I can add to the output of the query extra column with value if id exists in another table or not:
I need several things:
- Do a join between two tables
- Add a new column into the result output where I check if exists in the third table or not
My tables:
- announcement
- author
- chapter
announcement table
| id | author_id | date | group_id | ... |
author table
| id | name | email | ... |
chapter table
| id | announcement_id | ... |
This is what I have now. I did a left outer join and it works as I expected:
select announcement.id, announcement.date, author.id as publisher_id, author.name as publisher_name
from announcement
left outer join author
on announcement.author_id = author.id
where announcement.group_id = 123 and announcement.date >= '2022-06-01'::date;
with output:
| id | date | publisher_id | publisher_name |
| 1 | 2020-07-01 | 12 | John |
| 2 | 2020-07-04 | 123 | Arthur |
Now I can't find a solution of how to add an extra column with_chapters to the query response, where I will check if announcement.id exists in chapter table under announcement_id column.
For example, chapter table can have such data:
| id | announcement_id |
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
So we see that some announcements can appear in chapters several times (so i'm looking for at least 1 match). And some announcements doesn't have chapters at all.
Output finally should be like that:
| id | date | publisher_id | publisher_name | with_chapters |
| 1 | 2020-07-01 | 12 | John | true |
| 2 | 2020-07-04 | 123 | Arthur | false |
Thanks a lot for any help :)
release.publisher_idexpression coming from?