jeudi 30 juin 2016

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?


Aucun commentaire:

Enregistrer un commentaire