Daniel Calderon Mori Daniel Calderon Mori - 5 months ago 16
MySQL Question

How to join tables including all ids from left table but only showing information from the right table given certain where clause

I have an attendees table with the following structure:

+--------------+---------+
| attendee_id | others1 |
+--------------+---------+
| abcd | A |
| ghij | B |
| defg | C |
+--------------+---------+


And also an eventattendees table with the following structure:

+--------------+---------+----------+
| attendee_id | others2 | event_id |
+--------------+---------+----------+
| wxyz | D | 1 |
| mlno | E | 2 |
| defg | F | 3 |
| defg | G | 2 |
| abcd | H | 1 |
+--------------+---------+----------+


What I want is to create a query that, given some event_id, returns a join of these tables (by attendee_id) that includes all attendee ids from attendee table and also returns the information from the eventattendde tables which a match for that event_id. Say, for event_id 3:

+--------------+---------+---------+----------+
| attendee_id | others1 | others2 | event_id |
+--------------+---------+--------------------+
| abcd | A | null | null |
| ghij | B | null | null |
| defg | C | F | 3 |
+--------------+---------+--------------------+


How can I do that for mysql?

Answer

You need to put your where criteria in the join instead to respect the outer join.

select a.attendee_id, a.others1, e.others2, e.event_id
from attendees a
   left join eventattendees e on a.attendee_id = e.attendee_id and e.event_id = 3

If you put it in the where criteria, it will negate the outer join.