Problem with query join and numbers of rows

Databases
1

Hi there, I need your appreciated help.

If execute this select query:


SELECT * FROM `tbl_A` WHERE 1 AND `On_Off` = '1';

I have this output:


Affected rows: 2.962
Time: 0.312ms

If execute this select query with Join:


SELECT * FROM tbl_A A 
        JOIN tbl_CA CA ON CA.User_Number = A.User_Number
WHERE 1 
        AND `On_Off` = '1';

I have this other output:


Affected rows: 3.336
Time: 0.250ms

Why this difference?
Which this 3336-2962 = 374 records?

I need transfers this 2.962 records in other table MySQL when the field User_Number of table tbl_A corresponding with the field User_Number of table tbl_CA, but if condition of tbl_A On_Off = ‘1’ is respected.

Can you help me?
Thanks in advance, cheers.

This is my tables in MySQL:


/*
Navicat MySQL Data Transfer

Source Server         : _MySQL
Source Server Version : 50045
Source Host           : localhost:3306
Source Database       : db_

Target Server Type    : MYSQL
Target Server Version : 50045
File Encoding         : 65001

Date: 2011-08-18 11:02:32
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `tbl_A`
-- ----------------------------
DROP TABLE IF EXISTS `tbl_A`;
CREATE TABLE `tbl_A` (
  `xID` int(11) NOT NULL auto_increment,
  `User_Number` varchar(11) default NULL,
  `On_Off` tinyint(4) default '0',
  PRIMARY KEY  (`xID`),
  KEY `On_Off` (`On_Off`),
) ENGINE=MyISAM AUTO_INCREMENT=18637 DEFAULT CHARSET=latin1;




/*
Navicat MySQL Data Transfer

Source Server         : _MySQL
Source Server Version : 50045
Source Host           : localhost:3306
Source Database       : db_

Target Server Type    : MYSQL
Target Server Version : 50045
File Encoding         : 65001

Date: 2011-08-18 11:02:45
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `tbl_CA`
-- ----------------------------
DROP TABLE IF EXISTS `tbl_CA`;
CREATE TABLE `tbl_CA` (
  `ID` int(10) NOT NULL auto_increment,
  `User_Number` varchar(255) default NULL,
  PRIMARY KEY  USING BTREE (`ID`),
  KEY `User_Number` (`User_Number`),
) ENGINE=InnoDB AUTO_INCREMENT=24239 DEFAULT CHARSET=latin1;
2

because some rows in A match more than one row in CA

3

Is true! Thanks for your help!

Now the numbers of rows coincides:

SELECT * FROM `tbl_A` 
   WHERE 1 
      AND `On_Off` = '1' 
      AND User_Number > 0
 GROUP BY User_Number
 ORDER BY User_Number ASC;

Affected rows: 2943
Time: 0.141ms

##########################

SELECT CA.User_Number
     , A.User_Number
     , A.On_Off 
   FROM tbl_A A 
      JOIN tbl_CA CA ON CA.User_Number = A.User_Number
   WHERE 1 
      AND `On_Off` = '1' 
      AND User_Number > 0
 GROUP BY User_Number
 ORDER BY User_Number ASC;

Affected rows: 2943
Time: 0.079ms
4

well, duh

the tables still contain different numbers of rows, but you have suppressed this fact by using GROUP BY in both queries

what are you really trying to achieve?

5

Thanks, you’re right…

But I don’t understand… If I don’t use GROUP BY clause:


SELECT * FROM `tbl_A`  
   WHERE 1  
      AND `On_Off` = '1'  
      AND 
      (User_Number = '416673420'
      OR User_Number = '416177538'
      OR User_Number = '416081581')
 ORDER BY User_Number ASC;

I have this output:


User_Number 	On_Off
416081581	1
416177538	1
416673420	1
416673420	1



SELECT CA.User_Number 
     , A.User_Number 
     , A.On_Off  
   FROM tbl_A A  
      JOIN tbl_CA CA ON CA.User_Number = A.User_Number 
   WHERE 1  
      AND `On_Off` = '1'  
      AND 
      (User_Number = '416673420'
      OR User_Number = '416177538'
      OR User_Number = '416081581')
 ORDER BY User_Number ASC; 



CA.User_Number	A.User_Number	On_Off
416081581	416081581	1
416177538	416177538	1
416673420	416673420	1
416673420	416673420	1

The rows are always four… If I use GROUP BY clause the rows are always three… If execute this select query:


SELECT * FROM `tbl_A` 
    WHERE 1 AND `On_Off` = '1'
            AND User_Number > 0;

I have this output:


Affected rows: 2.962
Time: 0.312ms

If execute this select query with Join:


SELECT 
       CA.User_Number  
     , A.User_Number  
     , A.On_Off 
        FROM tbl_A A 
        JOIN tbl_CA CA ON CA.User_Number = A.User_Number
WHERE 1 
        AND `On_Off` = '1'
        AND User_Number > 0;

I have this other output:


Affected rows: 3.336
Time: 0.250ms

I don’t understand… :frowning:
I thinks all output of the queries is the same number of rows…

6

the answer is in post #2

:slight_smile: