SQL query, count the amount of existing values of a Foreign Key for each corresponding Primary Key in 1 query

Question

Example with 2 tables:

table admin:
    columns: | id_admin | email | password |
    values:  |     1    | ex@ex |   123    |
             |     2   | ex2@ex2 |   123   |

table news:
    columns: | id_news  | title  | content | admin_id_admin |
    values:  |   1      |  ex    | cont    |       1        |
             |   2      |  ex2   | cont2   |       1        |
             |   3      |  ex3   | cont3   |       2        |

what I want to do: Select all data from all admins and also the amount of news each has

The return would be something like this:

  | id_admin | email | password | count_news |
  |     1    | ex@ex |   123    |   2        |
  |     2   | ex2@ex2 |   123   |   1        |

Is there any way to accomplish this in a query?

How I thought of the query:

 SELECT a.id_admin, a.email, a.password, COUNT(b.id_news) AS count_news FROM admin a, news b WHERE a.id_admin = b.admin_id_admin

But it returns "NULL" for all fields except for "count_news".

Answer 1

The GROUP BY in his query and you can use a JOIN instead of putting all tables in the FROM:

Schema (Mysql v5.7)

CREATE TABLE admin (
  id_admin INTEGER,
  email    VARCHAR(100),
  password VARCHAR(100)
);

INSERT INTO admin(id_admin, email, password)
           VALUES(1, 'ex@ex', '123'),
                 (2, 'ex2@ex2', '123');

CREATE TABLE news (
  id_news        INTEGER,
  title          VARCHAR(100),
  content        VARCHAR(100),
  admin_id_admin INTEGER
);

INSERT INTO news(id_news, title, content, admin_id_admin)
          VALUES(1, 'ex', 'cont', 1),
                (2, 'ex2', 'cont2', 1),
                (3, 'ex3', 'cont3', 2);

---

Query

SELECT a.id_admin,
       a.email,
       a.password,
       COUNT(b.id_news) AS count_news
  FROM admin a
  LEFT JOIN news b ON a.id_admin = b.admin_id_admin
 GROUP BY a.id_admin, a.email, a.password;

---

Upshot

| id_admin | email   | password | count_news |
| -------- | ------- | -------- | ---------- |
| 1        | ex@ex   | 123      | 2          |
| 2        | ex2@ex2 | 123      | 1          |

---

See working on DB Fiddle.

Answer 2

Need to make a INNER JOIN between the tables... Try like this...

SELECT a.id_admin, a.email, a.password, COUNT(b.admin_id_admin) AS count_news FROM admin a
INNER JOIN news b on b.admin_id_admin = a.id_admin WHERE a.id_admin = b.admin_id_admin