C
Lack GROUP BY in your query and you can use one JOIN instead of putting all tables in 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);
QuerySELECT 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;
Results| id_admin | email | password | count_news |
| -------- | ------- | -------- | ---------- |
| 1 | ex@ex | 123 | 2 |
| 2 | ex2@ex2 | 123 | 1 |
See working on https://www.db-fiddle.com/ .
