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

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?

Answer

Use left join. The first table should be your main table and the second table returns null if no match found.

SELECT a.*, b.others2, b.event_id FROM attendees a LEFT JOIN eventattendees b ON a.attendee_id = b.attendee_id GROUP BY a.attendee_id

Test:

CREATE TABLE attendees
    (`attendee_id` varchar(10), `others1` varchar(10));

CREATE TABLE eventattendees
    (`attendee_id` varchar(10), `others2` varchar(10), `event_id` int);

INSERT INTO attendees
VALUES
    ('abcd', 'B'),
    ('ghij', 'A'),
    ('defg', 'C');

INSERT INTO eventattendees
VALUES
    ('wxyz', 'D', 1),
    ('mlno', 'E', 2),
    ('defg', 'F', 3);


SELECT a.*, b.others2, b.event_id FROM attendees a LEFT JOIN eventattendees b ON a.attendee_id = b.attendee_id GROUP BY a.attendee_id
Comments