Pfthb

https://imgur.com/O1v3O8w

The above image shows my first table called events.

https://imgur.com/7Hbsitl

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!

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

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