How to build a query so that I can find all events along with an extra bool column “attending” which is a join on attendees table?

I have a events table, and I have a events_attendees table which has the event_id and user_id.

For example, I want to find events around some user (user_id = 1) but also know whether or not they are attending through that additional column.

Here’s the query that would get me the pins where the user is attending, but again I am wondering how I can get this as part of the pins lookup.

select * from events
join events_attendees EA on events.id = EA.pin_id and EA.user_id = 656

How can I instead get all events plus an additional column attending which is whether or not the user is attending this event?

Answer

You could use exists:

select e.*, 
    exists(
        select 1 
        from events_attendees ea 
        where ea.pin_id = e.id and ea.user_id = 656
    ) attending
from events e

An alternative is a left join:

select e.*, (ea.pin_id is not null) attending
from events e
left join events_attendees ea on ea.pin_id = e.id and ea.user_id = 656