MYSQL returning the count of how many people logged an event

Question

https://i.stack.imgur.com/PommS.jpg

The above image shows my first table called events.

https://i.stack.imgur.com/xSBKX.jpg

This above table is linked to the events table and is used to check if a user logs an event. I want to obtain a count of how much each event was logged while displaying the event details. The problem I've encountered is that if an event has 0 logs it will not appear in the output.

This is the sql ive used:

SELECT event.`eventid`,
       event.`name`,
       `eventtime`,
       `location`,
       `datecreated`,
       `dateofevent`,
       `categoryname`,
       `description`,
       Count(participantlogs.eventid)
FROM   event,
       recreationalcategory,
       participantlogs
WHERE  recreationalcategory.categoryid = event.categoryid
       AND participantlogs.eventid = event.eventid
GROUP  BY event.eventid

Any help would be greatly appreciated!

No images please; use formatted text for posting your sample data and expected output. Refer this link on how to frame a good SQL question: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/q/333952/2469308) — Madhur Bhaiya, Nov 13 '18 at 20:43
Possible duplicate of [Displaying rows with count 0 with mysql group by](https://stackoverflow.com/questions/743456/displaying-rows-with-count-0-with-mysql-group-by) — Ruud Helderman, Nov 13 '18 at 20:45
A `LEFT JOIN` is probably what you're looking for. This way it will include the events that have 0 logs. Check out this [similar question](https://stackoverflow.com/questions/40918613/count-with-left-join-and-group-by-to-include-null-in-mysql). — Wrokar, Nov 13 '18 at 20:45
Please don't use Old comma based Implicit joins and use Modern [Explicit `Join` based syntax](https://stackoverflow.com/q/5654278/2469308) — Madhur Bhaiya, Nov 13 '18 at 20:49
Invalid GROUP BY... You typically GROUP BY all the columns you select, except those who are arguments to set functions. — jarlh, Nov 13 '18 at 20:59

spencer7593 · Accepted Answer · 2018-11-13T20:59:38.373

Ditch the old-school comma syntax for the join operation, and use JOIN keyword instead, relocate the join predicates to the ON clause. The JOIN keyword can be modified to with LEFT or RIGHT keyword to allow an outer join.

An outer join will return rows that don't have a match; exactly the use case described.

SELECT e.eventid
     , e.name
     , COUNT(p.eventid)
  FROM event e
  LEFT
  JOIN recreationalcategory c
    ON c.categoryid = e.categoryid
  LEFT
  JOIN participantlogs p
    ON p.eventid = e.eventid
 GROUP
    BY e.eventid
     , e.name

Another alternative would be to use a correlated subquery in the SELECT list rather than a join. Assuming eventid is PRIMARY KEY or UNIQUE KEY in event table, we can eliminate the GROUP BY clause...

SELECT e.eventid
     , e.name
     , ( SELECT COUNT(1)
           FROM participantlogs p
          WHERE p.eventid = e.eventid
       ) AS cnt
  FROM event e
  LEFT
  JOIN recreationalcategory c
    ON c.categoryid = e.categoryid

score 0 · Answer 2 · answered Nov 13 '18 at 20:55

A left join should do your job.

SELECT event.`eventid`,
       event.`name`,
       `eventtime`,
       `location`,
       `datecreated`,
       `dateofevent`,
       `categoryname`,
       `description`,
       Count(participantlogs.eventid)
FROM   event,
       recreationalcategory,
       participantlogs
WHERE  (recreationalcategory.categoryid = event.categoryid OR recreationalcategory.categoryid  is null)
       AND participantlogs.eventid = event.eventid
GROUP  BY event.eventid

on simply use left join

SELECT event.`eventid`,
           event.`name`,
           `eventtime`,
           `location`,
           `datecreated`,
           `dateofevent`,
           `categoryname`,
           `description`,
           Count(participantlogs.eventid)
     FROM   event 
     left join recreationalcategory 
       on recreationalcategory.categoryid = event.categoryid
     join participantlo on
       on participantlogs.eventid = event.eventid
    GROUP  BY event.eventid

invalid GROUP BY which can cause invalid results on MySQL without sql mode only_full_group_by. — Raymond Nijland, Nov 13 '18 at 21:28

MYSQL returning the count of how many people logged an event

2 Answers2