0

there are 3 tables:

table_o

CREATE TABLE `table_o` (
    `o_id` INT(11) NOT NULL AUTO_INCREMENT,
    `conf` VARCHAR(10) NOT NULL COLLATE 'utf8_general_ci',
    PRIMARY KEY (`o_id`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=6
;
o_id conf
1 no
2 yes
3 yes
4 yes
5 no

table_p

CREATE TABLE `table_p` (
    `p_id` INT(11) NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(255) NOT NULL COLLATE 'utf8_general_ci',
    PRIMARY KEY (`p_id`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=6
;
p_id title
1 p1
2 p2
3 p3
4 p4
5 p5

table_r

CREATE TABLE `table_r` (
    `r_id` INT(11) NOT NULL AUTO_INCREMENT,
    `p_id` INT(11) NOT NULL,
    `mods` INT(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`r_id`) USING BTREE,
    INDEX `FK_table_r_table_p` (`p_id`) USING BTREE,
    CONSTRAINT `FK_table_r_table_p` FOREIGN KEY (`p_id`) REFERENCES `newdb`.`table_p` (`p_id`) ON UPDATE RESTRICT ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=7
;
r_id p_id mods
1 1 0
2 1 1
3 3 1
4 4 0
5 2 1
6 3 0

subqueries are working perfectly:

SELECT tp.p_id,
       (SELECT COUNT(*)
       FROM table_o o
       WHERE o.conf = 'no') no_o,
       (SELECT COUNT(*)
       FROM table_r tr
       WHERE tr.mods = 0) no_r
FROM table_p tp
WHERE tp.p_id = '1';

output:

p_id no_o no_r
1 2 3

but multiple JOIN statements gave the wrong result

SELECT tp.p_id,
       COUNT(o.o_id) no_o,
       COUNT(tr.p_id) no_r
FROM table_p tp
JOIN table_o o
ON o.conf = 'no'
JOIN table_r tr
ON tr.mods = 0
WHERE tp.p_id = '1';

output:

p_id no_o no_r
1 6 6

Could you explain to me why this is happening?

philipxy
  • 14,867
  • 6
  • 39
  • 83
Gurgen
  • 3
  • 3
  • You don't say what you want the query to do. [mre] "this is happening" because that's how the language is defined. If you want to know what is wrong with your reasoning, you have to give it. Otherwise you're just asking us to rewrite the documentation with a bespoke tutorial with no idea what you already misunderstand. When you get a result that you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. – philipxy Jun 28 '21 at 12:07
  • I am trying to count the number of rows from 'table_o' and 'table_r' with some condition in 'WHERE' clause. I hoped that I could explain the problem as clearly as possible. If you look at the summary tables, you will see that the queries with JOIN operators are different from the results obtained with subqueries. – Gurgen Jun 28 '21 at 12:34
  • Please clarify via edits, not comments. I guess from your comment that you mean that you expect the 2nd query to return the same as the 1st. Please act on my previous comment. PS A [mre] includes cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. – philipxy Jun 28 '21 at 12:48
  • This seems likely to be a common error where people want some joins, each possibly involving a different key, of some subqueries, each possibly involving join and/or aggregation, but they erroneously try to do all the joining then all the aggregating or to aggregate over previous aggregations. Write separate aggregation subqueries over appropriate rows and/or aggregate (maybe distinct) case statements picking rows of joins; then join the subqueries together. [Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs](https://stackoverflow.com/a/45252750/3404097) – philipxy Jun 28 '21 at 12:50

0 Answers0