[MySQL 5.0.45-community-nt] JOIN Syntax

cms9651 · October 29, 2012, 1:46pm

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 · October 29, 2012, 1:58pm

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 · October 29, 2012, 2:04pm

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 · October 29, 2012, 3:03pm

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

why are you counting?

cms9651 · October 29, 2012, 3:07pm

thank you for help.

because in tbl_2 in the future I can have multiple rows with code equal to 380264.
I need count this rows.

cpradio · October 29, 2012, 3:18pm

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 · October 29, 2012, 3:24pm

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 · October 29, 2012, 3:27pm

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

thanks a lot, now the output is correct with your query.

r937 · October 29, 2012, 3:30pm

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