2 added 54 characters in body
source | link

From MySQL 8 on, you can use CTE - you may need to handle the nulls:

with sda (aI,aC) as (select da.`doc_id` I, COUNT(da.`doc_id`) C
  from `docs_scod_a` da,  `scod_a` a
  where a.id = da.`doc_id`
  group by da.`doc_id`
 ),
 sdb (bI,bC ) as (select db.`doc_id` I, COUNT(db.`doc_id`) C
   from `docs_scod_b` db, `scod_b` b
   where  b.`id` = db.`doc_id`
   group by db.`doc_id`
 )
SELECT d.`id`, sda.aC, sdb.bC
  FROM `docs` d
  left outer join sda on d.`id` = sda.aI
  left outer join sdb on d.`id` = sdb.bI

giving

id  aC      bC
1   3       3
2   3       1
3   2       3
4   null    1

https://www.db-fiddle.com/f/fBNDvSYXgFRfWU4778WvJB/0

From MySQL 8 on, you can use CTE - you may need to handle the nulls:

with sda (aI,aC) as (select da.`doc_id` I, COUNT(da.`doc_id`) C
  from `docs_scod_a` da,  `scod_a` a
  where a.id = da.`doc_id`
  group by da.`doc_id`
 ),
 sdb (bI,bC ) as (select db.`doc_id` I, COUNT(db.`doc_id`) C
   from `docs_scod_b` db, `scod_b` b
   where  b.`id` = db.`doc_id`
   group by db.`doc_id`
 )
SELECT d.`id`, sda.aC, sdb.bC
  FROM `docs` d
  left outer join sda on d.`id` = sda.aI
  left outer join sdb on d.`id` = sdb.bI

giving

id  aC      bC
1   3       3
2   3       1
3   2       3
4   null    1

From MySQL 8 on, you can use CTE - you may need to handle the nulls:

with sda (aI,aC) as (select da.`doc_id` I, COUNT(da.`doc_id`) C
  from `docs_scod_a` da,  `scod_a` a
  where a.id = da.`doc_id`
  group by da.`doc_id`
 ),
 sdb (bI,bC ) as (select db.`doc_id` I, COUNT(db.`doc_id`) C
   from `docs_scod_b` db, `scod_b` b
   where  b.`id` = db.`doc_id`
   group by db.`doc_id`
 )
SELECT d.`id`, sda.aC, sdb.bC
  FROM `docs` d
  left outer join sda on d.`id` = sda.aI
  left outer join sdb on d.`id` = sdb.bI

giving

id  aC      bC
1   3       3
2   3       1
3   2       3
4   null    1

https://www.db-fiddle.com/f/fBNDvSYXgFRfWU4778WvJB/0

1
source | link

From MySQL 8 on, you can use CTE - you may need to handle the nulls:

with sda (aI,aC) as (select da.`doc_id` I, COUNT(da.`doc_id`) C
  from `docs_scod_a` da,  `scod_a` a
  where a.id = da.`doc_id`
  group by da.`doc_id`
 ),
 sdb (bI,bC ) as (select db.`doc_id` I, COUNT(db.`doc_id`) C
   from `docs_scod_b` db, `scod_b` b
   where  b.`id` = db.`doc_id`
   group by db.`doc_id`
 )
SELECT d.`id`, sda.aC, sdb.bC
  FROM `docs` d
  left outer join sda on d.`id` = sda.aI
  left outer join sdb on d.`id` = sdb.bI

giving

id  aC      bC
1   3       3
2   3       1
3   2       3
4   null    1