SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    [MySQL 5.0.45-community-nt] JOIN Syntax

    Hi all, I hope your help.

    I have this two tables in MySQL:
    Code:
    DROP TABLE IF EXISTS `tbl_1`;
    CREATE TABLE `tbl_1` (
      `id` int(10) NOT NULL AUTO_INCREMENT,
      `MAC` varchar(3) DEFAULT NULL,
      `REG` varchar(3) DEFAULT NULL,
      `CODE` varchar(10) DEFAULT NULL,
      `NAME` varchar(2) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
    INSERT INTO `tbl_1` VALUES ('1', 'AAA', 'TOS', '380264', 'VF');
    INSERT INTO `tbl_1` VALUES ('2', 'BBB', 'TOS', '380264', 'VF');
    INSERT INTO `tbl_1` VALUES ('3', 'CCC', 'TOS', '380264', 'VF');
    INSERT INTO `tbl_1` VALUES ('4', 'DDD', 'TOS', '380264', 'VF');
    
    
    
    DROP TABLE IF EXISTS `tbl_2`;
    CREATE TABLE `tbl_2` (
      `ID` int(10) NOT NULL AUTO_INCREMENT,
      `CODE` varchar(10) DEFAULT NULL,
      `matr` varchar(10) DEFAULT NULL,
      PRIMARY KEY (`ID`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
    INSERT INTO `tbl_2` VALUES ('1', '380264', '112036');
    If tried this query:
    Code:
    SELECT
    	*
    FROM
    	tbl_2
    WHERE
    	Matr = '112036'
    AND Code = '380264';
    I have this output:
    Code:
    ID	CODE	MATR
    1	380264	112036
    If tried this join query:
    Code:
    SELECT
    	a.Matr,
            c.Name,
    	COUNT(*) `number`
    FROM
    	tbl_2 a
    JOIN tbl_1 c ON a.Code = c.Code
    GROUP BY
    	c.Code,
    	Matr,
    	a.Code;
    I have this other wrong output:
    Code:
    Matr	Name	number
    112036	VF	4
    I need this right output:
    Code:
    Matr	Name	number
    112036	VF	1
    Group for c.Code and a.Code is not sufficient ?

    Can you help me?
    Thanks in advance

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you joined tbl_1 to tbl_2 based on the code columns

    there are 4 rows in tbl_1 that match

    why do you want only 1 for the count?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thank you for help.

    because in tbl_2 I have only one row where code equal to 380264... the tbl_1 is the chronological table not normalized, but I don't have other possibility for extract the name of code...
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, so you want to extract the name of the code

    why are you counting?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thank you for help.
    Quote Originally Posted by r937 View Post
    why are you counting?
    because in tbl_2 in the future I can have multiple rows with code equal to 380264.
    I need count this rows.
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  6. #6
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,219
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cms9651 View Post
    If tried this join query:
    Code:
    SELECT
    	a.Matr,
            c.Name,
    	COUNT(*) `number`
    FROM
    	tbl_2 a
    JOIN tbl_1 c ON a.Code = c.Code
    GROUP BY
    	c.Code,
    	Matr,
    	a.Code;
    Maybe I am wrong, but I always thought it was a general rule across all databases that GROUP BY must contain all non-aggregate columns in your SELECT?

    So wouldn't it have to be
    Code:
    SELECT
    	a.Matr,
            c.Name,
    	COUNT(*) `number`
    FROM
    	tbl_2 a
    JOIN tbl_1 c ON a.Code = c.Code
    GROUP BY
    	a.Matr,
    	c.Name;

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT tbl_2.code
         , ( SELECT DISTINCT name
               FROM tbl_1
              WHERE code = tbl_2.code ) AS name
         , tbl_2.matr
         , count(*) AS `number`
      FROM tbl_2
    GROUP
        BY tbl_2.code
         , tbl_2.matr
    fyi, SELECT DISTINCT name will break the query if any code in tbl_1 has more than one name... your table is not normalized
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT tbl_2.code
         , ( SELECT DISTINCT name
               FROM tbl_1
              WHERE code = tbl_2.code ) AS name
         , tbl_2.matr
         , count(*) AS `number`
      FROM tbl_2
    GROUP
        BY tbl_2.code
         , tbl_2.matr
    fyi, SELECT DISTINCT name will break the query if any code in tbl_1 has more than one name... your table is not normalized
    thanks a lot, now the output is correct with your query.
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cpradio View Post
    Maybe I am wrong, but I always thought it was a general rule across all databases that GROUP BY must contain all non-aggregate columns in your SELECT?
    no, not mysql

    mysql has specifically extended this functionality, see GROUP BY and HAVING with Hidden Columns

    this "enhancement" has caused ~way~ more grief for novice developers than it has saved time and processing cycles for developers who know how to use it properly

    see also Debunking GROUP BY myths
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,219
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Ah, thanks for that. I figured as much since he didn't complain about the query throwing an error, but was stumped that the rule didn't exist. Thanks for the content, I've added it to my reading list.


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
  •