How to correctly compose a query with a subquery?

Question

MySQL

Friends. Tell me, what's wrong? The query returns zeros..
You need to get the number of news for each category..

SELECT ct.`id`, (SELECT count(`id`) FROM dle_post ps WHERE ps.category regexp 'ct.`id`' && approve = '1') as allnews_ FROM dle_category ct

Answer 1

try like this:

select 
  ct.id,
  count(1) as allnews_
from dle_category ct
left join dle_post ps on ps.id = ct.id
where true
  and ps.approve = '1'
group by ct.id

maybe another problem is that approve is not a string / varchar
and in general it can be done easier if you only display id, count(1) - it's all there in dle_post, sort of, isn't it?