MySQL: LEFT JOIN

Question

Hello I have 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 columns 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 a query from these two tables to see similiar output?

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

mdma · Accepted Answer · 2010-07-23 12:03:07Z

4

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.

answered Jul 23, 2010 at 12:03

mdma

57.9k12 gold badges97 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

waka_waka Over a year ago

why do you check if registration's idEvent is null? Once registration is made it always belongs to some event.. It's like : We have events and people register to them. So f.e. 1 event - 100 registrations (from 300 people let's say)

mdma Over a year ago

r.idEvent (and all of r's fields) is null when there is no matching registration for person 1. (When there is no matching r.idEvent=e.idEvent and r.idPerson = 1).

waka_waka Over a year ago

I get always NO, even if I change conditioon to WHEN NULL

mdma Over a year ago

You may need to write WHEN IS NOT NULL. Alternatively try IF (r.isEvent IS NULL, 'No', 'Yes') instead of the CASE...END.

Collectives™ on Stack Overflow

MySQL: LEFT JOIN

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related