3

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`;
1

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 |
  • @AliMalik updated, take a look at Query 4, I've removed the IN too feel free to add it if needed ;) – Blag Oct 14 '17 at 14:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.