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