An error occurred while querying multiple tables in Mysqljoin.

Because of the needs of the company's products, we need to implement A function, such as the basic information of the users in Table A and the user behavior in table A stored in Table B, table C and Table B are of the same nature. When the query is required, it can be sorted by the count results in Table B or table C, so we thought of join,...

Because of the needs of the company's products, we need to implement A function, such as the basic information of the users in Table A and the user behavior in table A stored in Table B, table C and Table B are of the same nature. When the query is required, the query results can be sorted by the count results in Table B or table C. Therefore, join occurs, but a problem occurs.

First, paste the data structure of the three tables

  CREATE TABLE `A` (  `id` int(11) NOT NULL auto_increment,  `username` varchar(255) default NULL,  PRIMARY KEY  (`id`),) ENGINE=MyISAM AUTO_INCREMENT=1;

  CREATE TABLE `B` (  `id` int(11) NOT NULL auto_increment,  `userid` int(11) default NULL,  `dosomething` varchar(255) default NULL,  PRIMARY KEY  (`id`),  KEY `userid` USING BTREE (`userid`)) ENGINE=MyISAM AUTO_INCREMENT=1;

  CREATE TABLE `C` (  `id` int(11) NOT NULL auto_increment,  `userid` int(11) default NULL,  `dosomething` varchar(255) default NULL,  PRIMARY KEY  (`id`),  KEY `userid` USING BTREE (`userid`)) ENGINE=MyISAM AUTO_INCREMENT=1;

Make your own attempt and find that the query results are different

SELECT u.id, COUNT(s.id) AS sapply, COUNT(uu.id) AS ftotal FROM A AS u RIGHT JOIN B AS s ON u.id = s.userid RIGHT JOIN C AS uu ON u.id = `uu`.`userid` GROUP BY `u`.`id` ORDER BY `ftotal` DESC LIMIT 10

The data is obviously problematic. separate the data and view the results.

SELECT u.id, COUNT(s.id) AS sapply FROM A AS u RIGHT JOIN B AS s ON u.id = s.userid GROUP BY `u`.`id` ORDER BY `sapply` DESC LIMIT 10

SELECT u.id, COUNT(uu.id) AS ftotal FROM A AS u RIGHT JOIN C AS uu ON u.id = uu.userid GROUP BY `u`.`id` ORDER BY `ftotal ` DESC LIMIT 10

Let's take a look. where is the problem? the first SQL statement is correct, but the result is incorrect.