2

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 |
4

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
  • I'm absolutly sorry about my blindness in the part of LEFT JOIN scod_x x ON x.id = dx.doc_id, Didn't notice untill now that I used the wrong column and fixed it to LEFT JOIN scod_x x ON x.id = dx.scod_x_id, Absolutly sorry about dragging you with my blindness and stupidity at this point. – Toleo Mar 10 '18 at 22:50
  • 1
    Should've seen it myself. Just make the same replacements in my query. – Gerard H. Pille Mar 11 '18 at 3:58
1

Extra conditions added to the scalar select - trying to answer the "IN()" problem:

SELECT d.`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 = 'AA'
 ) 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 = 'BB'
 ) sdb
FROM `docs`

Resulting in:

id  sda     sdb
1   3       0
2   0       1
3   0       0
4   0       0
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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.