CODEWITHSUNDEEP
sqlpostgresql
Askskd Rieid
Askskd Rieid

Reputation: 71

Selecting all Events, along with extra column "attending"

I have an Events table, and I have an Attendees table where I maintain the event_id and user_id.

I am trying to select all events, along with an additional column "attending" which I want to be the boolean result of whether the current user is attending that particular Event.

Here's the query that I am trying which seems to return the right result (against my basic test suite), and I am wondering if there is anything wrong with this or a better way.

Assuming the user who requested is user_id = 18..

SELECT events.id,
       EXISTS(SELECT 1 from attendees WHERE user_id = 18 AND event_id = events.id) AS "attending"
FROM events;

Results in:

| id       | attending      |
| -------- | -------------- |
| 1        | false          |
| 2        | true           |
| 7        | true           |

Upvotes: 1

Views: 372

Answers (1)

Frank Heikens
Frank Heikens

Reputation: 126971

SELECT events.id,
       event_id IS NOT NULL AS "attending"
FROM events
    LEFT JOIN attendees ON event_id = events.id AND user_id = 18;

And make sure you have indexes on user_id, event_id and events.id, to get performance.

Edit: Query is still a bit strange because it gets all events, and only checks for user_id 18 if he/she has been attending.

Upvotes: 2

Related Questions