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:

1. Do a join between two tables
2. 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 :)

CodePudding user response：

While EXISTS (subquery) is usually used in the WHERE clause, it returns an ordinary Boolean and so can be used in the select list.

SELECT blah1, blah2, 
       EXISTS (select 1 from chapter where chapter.announcement_id=announcement.id) as with_chapter
FROM ...