How to use INNER JOIN?

You did not say what is this self::$tableName .. what table is this? event category?

You want to count the number of categories in the event table, but also select the corresponding value in the category table? That's what I understood. Come on:

Tabela event:
+--------------+-------------+
| nome         | category_id |
+--------------+-------------+
| arroz        |      1      |
+--------------+-------------+
| patolino     |      1      |
+--------------+-------------+
| chinelo sujo |      2      |
+--------------+-------------+

Tabela category:
+----+----------------+------+
| id |      title     | desc |
+----+----------------+------+
| 1  |  Categoria UM  | nada |
+----+----------------+------+
| 2  | Categoria DOIS | ola  |
+----+----------------+------+

You can use this query:

SELECT category.*,
(SELECT COUNT(*) FROM event WHERE category_id = category.id) as total
FROM category

The result would be:

+----+----------------+------+-------+
| id |      title     | desc | total |
+----+----------------+------+-------+
| 1  |  Categoria UM  | nada |   2   |
+----+----------------+------+-------+
| 2  | Categoria DOIS | ola  |   1   |
+----+----------------+------+-------+

So I understand you want to make a SELECT with a JOIN between your event table and categories.

I think this resolves:

SELECT
  e.title AS event_title,
  e.description AS event_description,
  c.id AS event_category_id,
  c.name AS event_category_name,
  c.color AS event_category_color,
  c.icon AS event_category_icon
FROM event e 
  INNER JOIN category c ON
    c.id = e.category_id

Ready, in the same SELECT you are getting both the event data and the event category.

I just do not understand the part of the total of categories, you still want to present the total of categories registered in the same SELECT ?

If it is, it does not make much sense. It would be better to make another SELECT doing COUNT of categories.

SELECT count(*) FROM category

UPDATE

Well, it does not make sense because with this SELECT bringing events and categories together we are always searching for 1 event and 1 category corresponding to it per line.

If you want a COUNT of all your categories you have the option to put a subquery in SELECT by having it bring COUNT , see:

SELECT
  e.title AS event_title,
  e.description AS event_description,
  c.id AS event_category_id,
  c.name AS event_category_name,
  c.color AS event_category_color,
  c.icon AS event_category_icon,
  (SELECT COUNT(cc) FROM category cc) AS count_category
FROM event e 
  INNER JOIN category c ON
    c.id = e.category_id

Doing this will execute the query SELECT COUNT(cc) FROM category cc for each row returned with event and category, ie if your SELECT of events bring you 10 rows this subquery will be executed 10 times.

You can also try to do via LEFT JOIN or RIGHT JOIN , I do not know if it will work to be honest, but even then you would still have to GROUP BY of the other columns and all returned event rows and category would be the same as COUNT .