2

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 :)

2
  • 1
    "... on release.publisher_id = author.id ..." Where is the release.publisher_id expression coming from? Commented Aug 31, 2022 at 16:20
  • It's a typo, changed description, thanks Commented Aug 31, 2022 at 16:39

1 Answer 1

3

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 ...
Sign up to request clarification or add additional context in comments.

1 Comment

Quite a bit necro, but the question would have applied in 2022 also! Why would you use this instead of IN? E.g., SELECT <blah> WHERE announcement.id IN (SELECT announcement_id FROM chapter)? cf. postgresql.org/docs/current/functions-subquery.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.