lundi 4 juillet 2016

How to join tables including rows that doesn't find a match by using a non foreign key field

I have an attendees table with the following structure:

+--------------+---------+
| attendee_id  | others1 |
+--------------+---------+
|    abcd      | B       |
|    ghij      | A       |
|    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    |
+--------------+---------+----------+

What I want is to create a query that, given some event_id, returns a join of these tables (by attendee_id) but also returns rows with attendee table information for the attenddes ids which didn't found 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