How can I merge these two left joins into a single one?

Question

How can I merge these two left joins: http://sqlfiddle.com/#!9/1d2954/69/0

SELECT d.`id`, (adcount + bdcount)
FROM `docs` d

LEFT JOIN 
(
  SELECT da.`doc_id`, COUNT(da.`doc_id`) AS adcount FROM `docs_scod_a` da
  INNER JOIN `scod_a` a ON a.`id` = da.`scod_a_id`
  WHERE a.`ver_a` IN ('AA', 'AB')
  GROUP BY da.`doc_id`
) ad ON ad.`doc_id` = d.`id`

LEFT JOIN 
(
  SELECT db.`doc_id`, COUNT(db.`doc_id`) AS bdcount FROM `docs_scod_b` db
  INNER JOIN `scod_b` b ON b.`id` = db.`scod_b_id`
  WHERE b.`ver_b` IN ('BA', 'BB')
  GROUP BY db.`doc_id`
) bd ON bd.`doc_id` = d.`id`

to be a Single left join just to ease its use in my code, while making it no less slower?

Answer 1

Let me first emphasize that your method of doing the calculation is the better method. You have two separate dimensions and aggregating them separately is often the most efficient method for doing the calculation. It is also the most scalable method.

That said, your query should be equivalent to this version:

SELECT d.id,
       count(distinct a.id),
       count(distinct b.id) 
FROM docs d left join
     docs_scod_a da
     ON da.doc_id = d.id LEFT JOIN
     scod_a a
     ON a.id = da.scod_a_id AND a.ver_a IN ('AA', 'AB') LEFT JOIN
     docs_scod_b db
     ON db.doc_id = d.id LEFT JOIN
     scod_b b
     ON b.id = db.scod_b_id AND b.ver_b IN ('BA', 'BB')
GROUP BY d.id
ORDER BY d.id;

This query is more expensive than it looks, because the COUNT(DISTINCT) incurs additional overhead compared to COUNT().

And here is the SQL Fiddle.

And, because LEFT JOIN can return NULL values, your query is more correctly written as:

SELECT d.`id`, COALESCE(adcount, 0) + COALESCE(bdcount, 0)

If you were having problems with the results, this small change might fix those problems.