SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Problem with query join and numbers of rows

    Hi there, I need your appreciated help.

    If execute this select query:
    Code:
    SELECT * FROM `tbl_A` WHERE 1 AND `On_Off` = '1';
    I have this output:
    Code:
    Affected rows: 2.962
    Time: 0.312ms
    If execute this select query with Join:
    Code:
    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:
    Code:
    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:
    Code:
    /*
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    Why this difference?
    because some rows in A match more than one row in CA
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Is true! Thanks for your help!

    Now the numbers of rows coincides:

    PHP Code:
    SELECT FROM `tbl_A
       
    WHERE 1 
          
    AND `On_Off` = '1' 
          
    AND User_Number 0
     GROUP BY User_Number
     ORDER BY User_Number ASC
    ;

    Affected rows2943
    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 rows2943
    Time
    0.079ms 

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    Now the numbers of rows coincides
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Thanks, you're right...

    But I don't understand... If I don't use `GROUP BY` clause:
    PHP Code:
    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:
    Code:
    User_Number 	On_Off
    416081581	1
    416177538	1
    416673420	1
    416673420	1
    PHP Code:
    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
    Code:
    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:
    Code:
    SELECT * FROM `tbl_A` 
        WHERE 1 AND `On_Off` = '1'
                AND User_Number > 0;
    I have this output:
    Code:
    Affected rows: 2.962
    Time: 0.312ms
    If execute this select query with Join:
    Code:
    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:
    Code:
    Affected rows: 3.336
    Time: 0.250ms
    I don't understand...
    I thinks all output of the queries is the same number of rows....

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    I don't understand...
    the answer is in post #2

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •