[MySQL 5.0.45-community-nt] JOIN Syntax

[cms9651]

Hi all, I hope your help.

I have this two tables in MySQL:

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:

SELECT
	*
FROM
	tbl_2
WHERE
	Matr = '112036'
AND Code = '380264';

I have this output:

ID	CODE	MATR
1	380264	112036

If tried this join query:

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:

Matr	Name	number
112036	VF	4

I need this right output:

Matr	Name	number
112036	VF	1

Group for c.Code and a.Code is not sufficient ?

Can you help me?
Thanks in advance

[r937]

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?

[cms9651]

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...

[r937]

okay, so you want to extract the name of the code

why are you counting?

[cms9651]

thank you for help.

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.

[cpradio]

If tried this join query:

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

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;

[r937]

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

[cms9651]

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.

[r937]

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

[cpradio]

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.