MySQL - easy query, pleas help

Question

Hello I have this 2 tables :

Events (idEvent, eventName)

and

Registrations(idRegistration, idEvent, idPerson)

People register to events and I store their idPerson. Now let's say, we have 2 events (event1 and event2). I am person with id = 1 and I want to see all events and column that will say me, if I am registered. I mean output like :

idEvent  eventName  IamRegistered
1        event1     yes
2        event2     no

how can I write query from this two tables to see similiar output? thx

PS: I know SQL syntax but can't figure it out, something with left join probably

Answer 1

You're correct, it is a left join. The CASE expression outputs Yes or No depending upon if a matching record was found.

SELECT e.idEvent, e.EventName, (CASE r.idEvent WHEN NOT NULL THEN 'Yes' ELSE 'No' END) AS IsRegistered FROM Events e
  LEFT JOIN Registrations r ON r.idEvent=e.idEvent AND r.idPerson=1

It's important to have the idPerson=1 check in the JOIN clause rather than the Where clause, otherwise events that person 1 is not registred for will not be displayed.