Mysql – Using group by with left join and IN yields empty result

group byjoin;MySQL

I have two tables: invitees and invitee_information. invitees belong to events (third table event_id is a foreign key in invitees table).

I want to get the total count of invitees per event for which there is no information available yet (no record in the invitee_information table).

When I write the query using only one event_id in the where clause and anti-join pattern (IS NULL for invitee_id column to filter for rows having no information saved) the count is returned as expected for that given event_id:

SELECT COUNT(i.id)
FROM invitees i
LEFT JOIN invitee_information ii 
ON ii.invitee_id = i.id
WHERE ii.invitee_id IS NULL AND i.`event_id` = 18571 AND i.`invitation_sent` = 1;

Now in order to optimize the query to get this count data at DB level for multiple event_ids instead of getting this in a loop for each event_id, I used IN and passed multiple event_ids and used GROUP_BY event_id.

Expected result should be (if there is no data saved in invitee_information table for event_id 18569 :

event_id | count(i.id)
18569    |          0

But query result is always empty. My updated query is:

SELECT i.`event_id`, COUNT(i.id)
FROM invitees i
LEFT JOIN invitee_information ii 
ON i.id = ii.invitee_id
WHERE ii.invitee_id IS NULL AND i.`event_id` IN(18569,18571) AND i.`invitation_sent` = 1
GROUP BY i.`event_id`;

Best Answer

Not sure about your question but are you after this kind of result ?

| event_id | c |
|----------|---|
|    18569 | 3 |
|    18571 | 2 |
|    20000 | 0 |

If yes, then you need to use a SUM(IF(ii.invitee_id IS NULL, 1,0))

SELECT i.`event_id`,  SUM(IF(ii.invitee_id IS NULL, 1,0)) c
FROM invitees i
LEFT JOIN invitee_information ii 
  ON i.id = ii.invitee_id
WHERE 

  i.`event_id` IN(18569,18571,20000) 
  AND i.`invitation_sent` = 1
GROUP BY i.`event_id`

Full answer :

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE invitees
    (`id` int, `event_id` int, `invitation_sent` int)
;

INSERT INTO invitees
    (`id`, `event_id`, `invitation_sent`)
VALUES
    (1, 18569, 1),
    (2, 18569, 1),
    (3, 18569, 1),
    (4, 18569, 1),
    (5, 18571, 1),
    (6, 18571, 1),
    (7, 10000, 0),
    (8, 10000, 0),
    (9, 20000, 1)
;


CREATE TABLE events
    (`id` int)
;

INSERT INTO events
    (`id`)
VALUES
    (18569),
    (18571),
    (10000),
    (20000)
;


CREATE TABLE invitee_information
    (`invitee_id` int)
;

INSERT INTO invitee_information
    (`invitee_id`)
VALUES
    (9),
    (4)
;

Query 1:

-- Your one by one query
SELECT i.`event_id`, COUNT(i.id) c
FROM invitees i
LEFT JOIN invitee_information ii 
ON ii.invitee_id = i.id
WHERE ii.invitee_id IS NULL AND i.`event_id` = 18571 AND i.`invitation_sent` = 1

Results:

| event_id | c |
|----------|---|
|    18571 | 2 |

Query 2:

-- The IN only for count at least 1
SELECT i.`event_id`,  COUNT(i.id) c
FROM invitees i
LEFT JOIN invitee_information ii 
  ON i.id = ii.invitee_id
WHERE 
   ii.invitee_id IS NULL 
   AND 
  i.`event_id` IN(18569,18571,20000) 
  AND i.`invitation_sent` = 1
GROUP BY i.`event_id`

Results:

| event_id | c |
|----------|---|
|    18569 | 3 |
|    18571 | 2 |

Query 3:

-- The count for every event asked
SELECT i.`event_id`,  SUM(IF(ii.invitee_id IS NULL, 1,0)) c
FROM invitees i
LEFT JOIN invitee_information ii 
  ON i.id = ii.invitee_id
WHERE 
  i.`event_id` IN(18569,18571,20000) 
  AND i.`invitation_sent` = 1
GROUP BY i.`event_id`

Results:

| event_id | c |
|----------|---|
|    18569 | 3 |
|    18571 | 2 |
|    20000 | 0 |

Query 4:

-- If you want the count for every event whatever invitation_sent
SELECT i.`event_id`,  SUM(IF(ii.invitee_id IS NULL, 1,0)) c
FROM invitees i
LEFT JOIN invitee_information ii 
  ON i.id = ii.invitee_id
GROUP BY i.`event_id`

Results:

| event_id | c |
|----------|---|
|    10000 | 2 |
|    18569 | 3 |
|    18571 | 2 |
|    20000 | 0 |
Related Question