How to use INNER JOIN?

Question

I have a table called Event and another call Category need to count amount of categories coming from event->category_id, but I need db to understand and read.

TABELAS **event** |**Category_id** ||     TABELA ||**Category** ------- 
                                   ||
 ||     title     |  2             ||               name 
 ||     desc      |  3             ||               color 

---

I’ve tried it this way.

 SELECT * as event,SELECT COUNT(*) as total from".self::$tablename." 
 GROUP BY category_id INNER JOIN category ON category.id = category.category.id  ";

Anyone can help ?

Answer 1

You didn’t say what this is 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   |
+----+----------------+------+-------+

Answer 2

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

I think that solves:

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

All right, in the same SELECT you are taking both event data and category data from it.

I just didn’t understand the part of the total categories, you still want to present the total of categories registered in the same SELECT?

If so, it doesn’t make much sense. It would be better to make another SELECT making a COUNT of the categories.

SELECT count(*) FROM category

---

UPDATE

Well, it doesn’t make sense because with this SELECT bringing events and categories together we are searching in the bank always 1 event and 1 category corresponding to it by line.

If you want one COUNT of all its categories you have the option to put a subquery in the SELECT telling him to bring the 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

By doing this you 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 lines this subquery will run 10 times.

You can also try to do via LEFT JOIN or RIGHT JOIN, I don’t know if it’ll work out to be honest, but you’d still have to do the GROUP BY of the other columns and on all rows returned from event and category would come the same number of COUNT.