MySQL SELECT in Merged 3-Tables Relation with IN() Clause and COUNT

join;mariadbMySQL

In a 3-Tables relation Query, I have this following two Queries for each relation:

http://sqlfiddle.com/#!9/0bcc34/3/0

SELECT d.`id`, COUNT(da.`doc_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`

WHERE a.`ver_a` IN ('AA', 'AB')

GROUP BY d.`id`;

| id | COUNT(da.`doc_id`) |
|----|--------------------|
|  1 |                  2 |
|  2 |                  1 |
|  3 |                  2 |

http://sqlfiddle.com/#!9/d89a4e/1/0

SELECT d.`id`, COUNT(db.`doc_id`)
FROM `docs` d

LEFT JOIN `docs_scod_b` db ON db.`doc_id` = d.`id`
LEFT JOIN `scod_b` b ON b.id = db.`scod_b_id`

WHERE b.`ver_b` IN ('BA', 'BB')

GROUP BY d.`id`;

| id | COUNT(db.`doc_id`) |
|----|--------------------|
|  1 |                  2 |
|  2 |                  1 |
|  3 |                  2 |

What I want to Do is to merge both Queries into a Single one That will not only use (3-Tables)x2 only, So I've tried the following Query:

http://sqlfiddle.com/#!9/1d2954/2/0

SELECT d.`id`, COUNT(da.`doc_id`), COUNT(db.`doc_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`

LEFT JOIN `docs_scod_b` db ON db.`doc_id` = d.`id`
LEFT JOIN `scod_b` b ON b.`id` = db.`scod_b_id`

WHERE a.ver_a IN ('AC', 'AB') AND b.ver_b IN ('BA', 'BB')

GROUP BY d.`id`;

What I expected to get from this Query was the following results:

| id | COUNT(da.`doc_id`) | COUNT(db.`doc_id`) |
|----|--------------------|--------------------|
|  1 |                  2 |                  2 |
|  2 |                  1 |                  1 |
|  3 |                  2 |                  2 |

But Instead of That I got

| id | COUNT(da.`doc_id`) | COUNT(db.`doc_id`) |
|----|--------------------|--------------------|
|  1 |                  4 |                  4 |
|  3 |                  2 |                  2 |

Is the Problm on the COUNT only or Am I using the wrong method to SELECT data from 3-Tables Relation?

So in Berif: How can I select data from 3-Tables Relation, And merge them together, Then COUNT result of each relation,

In the end I'll SUM the count for each row into one, So the final result expection overall after all of this is supposed to be

| id | SUM(COUNT(da.`doc_id`) + COUNT(db.`doc_id`))|
|----|---------------------------------------------|
|  1 |                                           4 |
|  2 |                                           2 |
|  3 |                                           4 |

Best Answer

You're multiplying the joins, and you get what you ask for. Scalar selects may be what you want:

select X.id, X.sda + X.sdb sumsummerdesum from (
SELECT d.`id`  id,
 (select COUNT(da.`doc_id`)
  from `docs_scod_a` da,  `scod_a` a
  where da.`doc_id` = d.`id`
    and a.id = da.`doc_id`
    and a.ver_a in ( 'AA', 'AC')
 ) sda,
 (select COUNT(db.`doc_id`)
   from `docs_scod_b` db, `scod_b` b
   where db.`doc_id` = d.`id`
     and b.`id` = db.`doc_id`
     and b.ver_b in ('BA','BB')
 ) sdb
FROM `docs` d
) X

http://sqlfiddle.com/#!9/fee4c6/39

id  sumsummerdesum
1   6
2   1
3   2
4   0
Related Question