Revisions to SELECT in 2 merged 3-Tables relation with IN() clause and a COUNT for each relation

Mod Moved Comments To Chat

occurred Mar 10, 2018 at 23:46

solving my blindness.

Source Link

edited Mar 10, 2018 at 22:49

318
1
4
13

http://sqlfiddle.com/#!9/f9a33f/5/0 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.`doc_id``scod_a_id`

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

GROUP BY d.`id`;

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

http://sqlfiddle.com/#!9/fee4c6/33/0 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.`doc_id``scod_b_id`

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

GROUP BY d.`id`;

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

http://sqlfiddle.com/#!9/fee4c6/36/0 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.`doc_id``scod_a_id`

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

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

GROUP BY d.`id`;

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

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

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

http://sqlfiddle.com/#!9/f9a33f/5/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.`doc_id`

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

GROUP BY d.`id`;

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

http://sqlfiddle.com/#!9/fee4c6/33/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.`doc_id`

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

GROUP BY d.`id`;

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

http://sqlfiddle.com/#!9/fee4c6/36/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.`doc_id`

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

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

GROUP BY d.`id`;

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

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

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

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 |

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`;

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

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

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

added 56 characters in body

Source Link

edited Mar 10, 2018 at 12:49

Toleo

318
1
4
13

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 |                                           6 |
|  2 |                                           1 |
|  3 |                                           2 |

So in Berif: How can I select data from 3-Tables Relation, And merge them together, Then COUNT result of each 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 |                                           6 |
|  2 |                                           1 |
|  3 |                                           2 |

fixing the example givin.

Source Link

edited Mar 10, 2018 at 12:41

Toleo

318
1
4
13

http://sqlfiddle.com/#!9/fee4c6/34/0 http://sqlfiddle.com/#!9/fee4c6/36/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.`doc_id`

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

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

GROUP BY d.`id`;

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

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

http://sqlfiddle.com/#!9/fee4c6/34/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.`doc_id`

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

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

GROUP BY d.`id`;

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

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

http://sqlfiddle.com/#!9/fee4c6/36/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.`doc_id`

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

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

GROUP BY d.`id`;

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

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

edited title

Link

edited Mar 10, 2018 at 12:33

Toleo

318
1
4
13

Loading

deleted 504 characters in body

Source Link

edited Mar 10, 2018 at 12:27

Toleo

318
1
4
13

Loading

added 265 characters in body

Source Link

edited Mar 10, 2018 at 12:12

Toleo

318
1
4
13

Loading

adding IN clause example & mariadb tag

Source Link

edited Mar 10, 2018 at 12:05

Toleo

318
1
4
13

Loading

adding IN clause example & mariadb tag

Source Link

edited Mar 10, 2018 at 11:55

Toleo

318
1
4
13

Loading

added 36 characters in body

Source Link

edited Mar 10, 2018 at 2:15

Toleo

318
1
4
13

Loading

Source Link

asked Mar 9, 2018 at 22:21

Toleo

318
1
4
13

Loading

Stack Exchange Network

Return to Question