LEFT OUTER JOIN syntax

cms9651 · February 23, 2012, 2:12pm

Hi there.

My problem today is ‘LEFT OUTER JOIN’ syntax.

I try in mysql this simple query :


SELECT
       COUNT(*) `n`
      ,t
  FROM tbl_x x
  LEFT OUTER JOIN tbl_y y
    ON z.t = y.t
  GROUP BY y.t

In the output I need see all rows of the tbl_x but on the contrary I see only records called for the join ‘x’ and ‘y’.

Your help would be very appreciated.

r937 · February 23, 2012, 4:09pm

you got your COUNT wrong, your ON condition wrong, and your GROUP BY backwards

try it like this –

SELECT x.t
     , COUNT(y.t) `n`
  FROM tbl_x x 
LEFT OUTER 
  JOIN tbl_y y 
    ON y.t = x.t
GROUP 
    BY x.t

cms9651 · February 23, 2012, 6:20pm

thanks a lot, your suggestion working.

now I try this statement join three tables:


SELECT
       COUNT(y.t) `n`
  FROM tbl_x x
  LEFT JOIN tbl_x y
  LEFT JOIN tbl_y z
    ON z.t = y.t
    ON y.A = x.A
   AND y.M = x.M -1
   AND z.t = x.t
 WHERE 1
  AND (dateFiels BETWEEN MAKEDATE(YEAR(CURDATE()),1) AND ADDDATE(CURDATE(),INTERVAL 0 YEAR))
  GROUP BY y.t;

Output:

If I try this statement:


SELECT
       COUNT(y.t) `n`
  FROM tbl_x x
  LEFT JOIN tbl_x y
  LEFT OUTER JOIN tbl_y z
    ON z.t = y.t
    ON y.A = x.A
   AND y.M = x.M -1
   AND z.t = x.t
 WHERE 1
  AND (dateField BETWEEN MAKEDATE(YEAR(CURDATE()),1) AND CURDATE())
  GROUP BY y.t;

The output not change… I can only get it to show the record of join ‘x’ and ‘y’.
I’d like it to show all record even when count is zero in the tbl_y.

r937 · February 23, 2012, 6:30pm

sorry, too vague

please dump some actual rows from your actual tables

cms9651 · February 23, 2012, 8:17pm

… is true… dump my tables mysql:

DROP TABLE IF EXISTS `tbl_x`;
CREATE TABLE `tbl_x` (
  `t` varchar(100) DEFAULT NULL,
  `M` int(1) DEFAULT NULL,
  `A` int(4) DEFAULT NULL,
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;

-- ----------------------------
-- RecorSAR of tbl_x
-- ----------------------------
INSERT INTO `tbl_x` VALUES ('CMP4M', '1', '2011', '1');
INSERT INTO `tbl_x` VALUES ('CMP4N', '1', '2011', '2');
INSERT INTO `tbl_x` VALUES ('CMP4O', '1', '2011', '3');
INSERT INTO `tbl_x` VALUES ('CMP4P', '1', '2011', '4');
INSERT INTO `tbl_x` VALUES ('CMP6M', '1', '2011', '5');
INSERT INTO `tbl_x` VALUES ('CMP6N', '1', '2011', '6');
INSERT INTO `tbl_x` VALUES ('CMP6O', '1', '2011', '7');
INSERT INTO `tbl_x` VALUES ('CMP6P', '1', '2011', '8');
INSERT INTO `tbl_x` VALUES ('CMPSR', '1', '2011', '9');
INSERT INTO `tbl_x` VALUES ('CMPER', '1', '2011', '10');
INSERT INTO `tbl_x` VALUES ('CMPVM', '1', '2011', '11');
INSERT INTO `tbl_x` VALUES ('CMPSA', '1', '2011', '12');
INSERT INTO `tbl_x` VALUES ('CMP4M', '2', '2011', '13');
INSERT INTO `tbl_x` VALUES ('CMP4N', '2', '2011', '14');
INSERT INTO `tbl_x` VALUES ('CMP4O', '2', '2011', '15');
INSERT INTO `tbl_x` VALUES ('CMP4P', '2', '2011', '16');
INSERT INTO `tbl_x` VALUES ('CMP6M', '2', '2011', '17');
INSERT INTO `tbl_x` VALUES ('CMP6N', '2', '2011', '18');
INSERT INTO `tbl_x` VALUES ('CMP6O', '2', '2011', '19');
INSERT INTO `tbl_x` VALUES ('CMP6P', '2', '2011', '20');
INSERT INTO `tbl_x` VALUES ('CMPSR', '2', '2011', '21');
INSERT INTO `tbl_x` VALUES ('CMPER', '2', '2011', '22');
INSERT INTO `tbl_x` VALUES ('CMPVM', '2', '2011', '23');
INSERT INTO `tbl_x` VALUES ('CMPSA', '2', '2011', '24');
INSERT INTO `tbl_x` VALUES ('TOS1M', '1', '2011', '25');
INSERT INTO `tbl_x` VALUES ('TOS1N', '1', '2011', '26');
INSERT INTO `tbl_x` VALUES ('TOS1O', '1', '2011', '27');
INSERT INTO `tbl_x` VALUES ('TOS1P', '1', '2011', '28');
INSERT INTO `tbl_x` VALUES ('TOS1Q', '1', '2011', '29');
INSERT INTO `tbl_x` VALUES ('TOS5M', '1', '2011', '30');
INSERT INTO `tbl_x` VALUES ('TOS5N', '1', '2011', '31');
INSERT INTO `tbl_x` VALUES ('TOS5P', '1', '2011', '32');
INSERT INTO `tbl_x` VALUES ('TOS5Q', '1', '2011', '33');
INSERT INTO `tbl_x` VALUES ('TOS5R', '1', '2011', '34');
INSERT INTO `tbl_x` VALUES ('TOS6M', '1', '2011', '35');
INSERT INTO `tbl_x` VALUES ('TOS6N', '1', '2011', '36');
INSERT INTO `tbl_x` VALUES ('TOS6O', '1', '2011', '37');
INSERT INTO `tbl_x` VALUES ('TOSSR', '1', '2011', '38');
INSERT INTO `tbl_x` VALUES ('TOSER', '1', '2011', '39');
INSERT INTO `tbl_x` VALUES ('TOSVM', '1', '2011', '40');
INSERT INTO `tbl_x` VALUES ('TOSSA', '1', '2011', '41');
INSERT INTO `tbl_x` VALUES ('TOS1M', '2', '2011', '42');
INSERT INTO `tbl_x` VALUES ('TOS1N', '2', '2011', '43');
INSERT INTO `tbl_x` VALUES ('TOS1O', '2', '2011', '44');
INSERT INTO `tbl_x` VALUES ('TOS1P', '2', '2011', '45');
INSERT INTO `tbl_x` VALUES ('TOS1Q', '2', '2011', '46');
INSERT INTO `tbl_x` VALUES ('TOS5M', '2', '2011', '47');
INSERT INTO `tbl_x` VALUES ('TOS5N', '2', '2011', '48');
INSERT INTO `tbl_x` VALUES ('TOS5P', '2', '2011', '49');
INSERT INTO `tbl_x` VALUES ('TOS5Q', '2', '2011', '50');
INSERT INTO `tbl_x` VALUES ('TOS5R', '2', '2011', '51');
INSERT INTO `tbl_x` VALUES ('TOS6M', '2', '2011', '52');
INSERT INTO `tbl_x` VALUES ('TOS6N', '2', '2011', '53');
INSERT INTO `tbl_x` VALUES ('TOS6O', '2', '2011', '54');
INSERT INTO `tbl_x` VALUES ('TOSSR', '2', '2011', '55');
INSERT INTO `tbl_x` VALUES ('TOSER', '2', '2011', '56');
INSERT INTO `tbl_x` VALUES ('TOSVM', '2', '2011', '57');
INSERT INTO `tbl_x` VALUES ('TOSSA', '2', '2011', '58');









DROP TABLE IF EXISTS `tbl_y`;
CREATE TABLE `tbl_y` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dateField` date DEFAULT NULL,
  `t` varchar(4) DEFAULT NULL,
  UNIQUE KEY `id` (`id`),
  KEY `t` (`t`)
) ENGINE=MyISAM AUTO_INCREMENT=162 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of tbl_y
-- ----------------------------
INSERT INTO `tbl_y` VALUES ('1', '2011-12-21', 'TOS1M');
INSERT INTO `tbl_y` VALUES ('2', '2010-01-20', 'TOS5P');
INSERT INTO `tbl_y` VALUES ('3', '2010-01-18', 'SAR1M');
INSERT INTO `tbl_y` VALUES ('4', '2010-08-09', 'SAR1M');
INSERT INTO `tbl_y` VALUES ('5', '2010-01-10', 'LAZ7O');
INSERT INTO `tbl_y` VALUES ('6', '2011-12-13', 'TOS6O');
INSERT INTO `tbl_y` VALUES ('7', '2010-07-01', 'TOS5M');
INSERT INTO `tbl_y` VALUES ('8', '2007-12-12', 'TOS6M');
INSERT INTO `tbl_y` VALUES ('9', '2010-06-06', 'CMP6N');
INSERT INTO `tbl_y` VALUES ('10', '2010-07-18', 'CMP6O');
INSERT INTO `tbl_y` VALUES ('11', '2011-02-22', 'TOS1N');
INSERT INTO `tbl_y` VALUES ('12', '2008-06-16', 'LAZ00');
INSERT INTO `tbl_y` VALUES ('13', '2008-08-07', 'LAZ4M');
INSERT INTO `tbl_y` VALUES ('14', '2010-04-16', 'CMP6M');
INSERT INTO `tbl_y` VALUES ('15', '2010-05-20', 'CMP4P');
INSERT INTO `tbl_y` VALUES ('16', '2009-12-18', 'TOS5N');
INSERT INTO `tbl_y` VALUES ('17', '2011-12-19', 'TOS1N');
INSERT INTO `tbl_y` VALUES ('18', '2010-12-27', 'LAZ4M');
INSERT INTO `tbl_y` VALUES ('19', '2006-09-18', 'LAZ6M');
INSERT INTO `tbl_y` VALUES ('20', '2011-03-01', 'TOS6O');
INSERT INTO `tbl_y` VALUES ('21', '2011-06-23', 'SAR1N');
INSERT INTO `tbl_y` VALUES ('22', '2011-02-26', 'TOS5N');
INSERT INTO `tbl_y` VALUES ('23', '2010-05-07', 'CMP4P');
INSERT INTO `tbl_y` VALUES ('24', '2009-05-13', 'LAZ4P');
INSERT INTO `tbl_y` VALUES ('25', '2008-10-15', 'SAR1O');
INSERT INTO `tbl_y` VALUES ('26', '2010-01-12', 'SAR1Q');
INSERT INTO `tbl_y` VALUES ('27', '2010-09-07', 'TOS00');
INSERT INTO `tbl_y` VALUES ('28', '2010-09-07', 'TOS00');
INSERT INTO `tbl_y` VALUES ('29', '2009-11-06', 'LAZ6N');
INSERT INTO `tbl_y` VALUES ('30', '2011-08-19', 'CMP6M');
INSERT INTO `tbl_y` VALUES ('31', '2010-10-20', 'TOS1M');
INSERT INTO `tbl_y` VALUES ('32', '2010-04-14', 'TOS5M');
INSERT INTO `tbl_y` VALUES ('33', '2010-12-02', 'TOS5M');
INSERT INTO `tbl_y` VALUES ('34', '2009-09-16', 'CMP6O');
INSERT INTO `tbl_y` VALUES ('35', '2010-02-10', 'CMP6P');
INSERT INTO `tbl_y` VALUES ('36', '2009-06-03', 'CMP4N');
INSERT INTO `tbl_y` VALUES ('37', '2010-03-22', 'TOS1Q');
INSERT INTO `tbl_y` VALUES ('38', '2010-08-04', 'TOS1Q');
INSERT INTO `tbl_y` VALUES ('39', '2010-09-06', 'SAR1M');
INSERT INTO `tbl_y` VALUES ('40', '2011-07-06', 'CMP6M');
INSERT INTO `tbl_y` VALUES ('41', '2011-07-06', 'CMP6M');
INSERT INTO `tbl_y` VALUES ('42', '2011-07-11', 'CMP6P');
INSERT INTO `tbl_y` VALUES ('43', '2010-07-30', 'CMP6M');
INSERT INTO `tbl_y` VALUES ('44', '2010-04-23', 'SAR00');
INSERT INTO `tbl_y` VALUES ('45', '2010-11-08', 'SAR00');
INSERT INTO `tbl_y` VALUES ('46', '2010-01-21', 'TOS1N');
INSERT INTO `tbl_y` VALUES ('47', '2010-01-04', 'TOS1O');
INSERT INTO `tbl_y` VALUES ('48', '2010-12-14', 'TOS1O');
INSERT INTO `tbl_y` VALUES ('49', '2011-03-15', 'TOS1P');
INSERT INTO `tbl_y` VALUES ('50', '2011-01-24', 'TOS1Q');
INSERT INTO `tbl_y` VALUES ('51', '2011-11-10', 'TOS5M');
INSERT INTO `tbl_y` VALUES ('52', '2011-05-05', 'TOS5Q');
INSERT INTO `tbl_y` VALUES ('53', '2011-07-07', 'TOS6O');
INSERT INTO `tbl_y` VALUES ('54', '2010-11-15', 'LAZ2M');
INSERT INTO `tbl_y` VALUES ('55', '2010-05-14', 'LAZ2N');
INSERT INTO `tbl_y` VALUES ('56', '2011-09-09', 'LAZ2N');
INSERT INTO `tbl_y` VALUES ('57', '2010-11-12', 'LAZ4M');
INSERT INTO `tbl_y` VALUES ('58', '2010-11-12', 'LAZ4M');
INSERT INTO `tbl_y` VALUES ('59', '2011-06-08', 'LAZ4M');
INSERT INTO `tbl_y` VALUES ('60', '2011-08-08', 'LAZ4O');
INSERT INTO `tbl_y` VALUES ('61', '2011-05-25', 'LAZ4P');
INSERT INTO `tbl_y` VALUES ('62', '2009-09-21', 'LAZ4Q');
INSERT INTO `tbl_y` VALUES ('63', '2010-09-18', 'LAZ4Q');
INSERT INTO `tbl_y` VALUES ('64', '2011-02-08', 'LAZ4Q');
INSERT INTO `tbl_y` VALUES ('65', '2010-01-21', 'LAZ7M');
INSERT INTO `tbl_y` VALUES ('66', '2010-12-20', 'LAZ7M');
INSERT INTO `tbl_y` VALUES ('67', '2011-12-13', 'LAZ7O');
INSERT INTO `tbl_y` VALUES ('68', '2010-03-11', 'CMP4M');
INSERT INTO `tbl_y` VALUES ('69', '2010-03-11', 'CMP4M');
INSERT INTO `tbl_y` VALUES ('70', '2011-04-04', 'CMP4P');
INSERT INTO `tbl_y` VALUES ('71', '2011-07-13', 'SAR1O');
INSERT INTO `tbl_y` VALUES ('72', '2010-03-04', 'LAZ4N');
INSERT INTO `tbl_y` VALUES ('73', '2009-06-18', 'LAZ7N');
INSERT INTO `tbl_y` VALUES ('74', '2011-03-09', 'SAR1N');
INSERT INTO `tbl_y` VALUES ('75', '2011-08-08', 'LAZ00');
INSERT INTO `tbl_y` VALUES ('76', '2011-08-20', 'TOS1N');
INSERT INTO `tbl_y` VALUES ('77', '2010-11-18', 'TOS5P');
INSERT INTO `tbl_y` VALUES ('78', '2011-08-23', 'TOS6M');
INSERT INTO `tbl_y` VALUES ('79', '2011-05-19', 'LAZ7P');
INSERT INTO `tbl_y` VALUES ('80', '2010-05-20', 'SAR1Q');
INSERT INTO `tbl_y` VALUES ('81', '2011-09-05', 'TOS1Q');
INSERT INTO `tbl_y` VALUES ('82', '2011-05-23', 'TOS5M');
INSERT INTO `tbl_y` VALUES ('83', '2011-06-27', 'TOS5N');
INSERT INTO `tbl_y` VALUES ('84', '2011-02-23', 'LAZ00');
INSERT INTO `tbl_y` VALUES ('85', '2011-08-30', 'LAZ6N');
INSERT INTO `tbl_y` VALUES ('86', '2010-04-14', 'SAR1Q');
INSERT INTO `tbl_y` VALUES ('87', '2011-03-13', 'SAR1M');
INSERT INTO `tbl_y` VALUES ('88', '2011-03-05', 'SAR1P');
INSERT INTO `tbl_y` VALUES ('89', '2011-08-10', 'SAR1Q');
INSERT INTO `tbl_y` VALUES ('90', '2011-10-03', 'TOS00');
INSERT INTO `tbl_y` VALUES ('91', '2011-10-03', 'TOS00');
INSERT INTO `tbl_y` VALUES ('92', '2010-02-19', 'CMP4M');
INSERT INTO `tbl_y` VALUES ('93', '2009-06-18', 'CMP4O');
INSERT INTO `tbl_y` VALUES ('94', '2009-04-18', 'TOS1M');
INSERT INTO `tbl_y` VALUES ('95', '2010-03-05', 'TOS00');
INSERT INTO `tbl_y` VALUES ('96', '2010-03-05', 'TOS00');
INSERT INTO `tbl_y` VALUES ('97', '2011-03-20', 'SAR1M');
INSERT INTO `tbl_y` VALUES ('98', '2009-07-22', 'LAZ2M');
INSERT INTO `tbl_y` VALUES ('99', '2010-03-18', 'TOS1O');
INSERT INTO `tbl_y` VALUES ('100', '2011-12-22', 'TOS1Q');
INSERT INTO `tbl_y` VALUES ('101', '2011-02-15', 'TOS5P');
INSERT INTO `tbl_y` VALUES ('102', '2010-07-29', 'TOS5R');
INSERT INTO `tbl_y` VALUES ('103', '2011-06-13', 'TOS5R');
INSERT INTO `tbl_y` VALUES ('104', '2011-02-24', 'LAZ4P');
INSERT INTO `tbl_y` VALUES ('105', '2010-09-21', 'LAZ4N');
INSERT INTO `tbl_y` VALUES ('106', '2010-05-24', 'LAZ7M');
INSERT INTO `tbl_y` VALUES ('107', '2009-11-04', 'TOS1P');
INSERT INTO `tbl_y` VALUES ('108', '2008-05-06', 'TOS5Q');
INSERT INTO `tbl_y` VALUES ('109', '2009-11-20', 'TOS5R');
INSERT INTO `tbl_y` VALUES ('110', '2007-05-30', 'TOS6N');
INSERT INTO `tbl_y` VALUES ('111', '2009-11-19', 'TOS6O');
INSERT INTO `tbl_y` VALUES ('112', '2011-04-12', 'CMP4P');
INSERT INTO `tbl_y` VALUES ('113', '2009-03-23', 'LAZ4O');
INSERT INTO `tbl_y` VALUES ('114', '2010-03-11', 'LAZ7P');
INSERT INTO `tbl_y` VALUES ('115', '2011-09-21', 'LAZ4O');
INSERT INTO `tbl_y` VALUES ('116', '2009-08-05', 'CMP00');
INSERT INTO `tbl_y` VALUES ('117', '2011-10-04', 'TOS1Q');
INSERT INTO `tbl_y` VALUES ('118', '2009-06-09', 'SAR1N');
INSERT INTO `tbl_y` VALUES ('119', '2009-09-23', 'SAR1P');
INSERT INTO `tbl_y` VALUES ('155', '2011-08-22', 'CMP6O');
INSERT INTO `tbl_y` VALUES ('156', '2011-09-06', 'CMP4M');
INSERT INTO `tbl_y` VALUES ('157', '2011-09-26', 'CMP6O');
INSERT INTO `tbl_y` VALUES ('158', '2011-10-16', 'CMP6M');
INSERT INTO `tbl_y` VALUES ('159', '2011-11-30', 'CMP6P');
INSERT INTO `tbl_y` VALUES ('160', '2011-11-30', 'CMP6O');
INSERT INTO `tbl_y` VALUES ('161', '2011-12-12', 'CMP00');



SELECT 
       COUNT(y.t) `n`    
  FROM tbl_x x 
  LEFT JOIN tbl_x y 
  LEFT OUTER JOIN tbl_y z
    ON z.t = y.t
    ON y.A = x.A
   AND y.M = x.M + 1 
   AND z.t = x.t
 WHERE 1
  AND (dateField BETWEEN MAKEDATE(YEAR(CURDATE())-1,1) AND ADDDATE(CURDATE(),INTERVAL - 1 YEAR))
  GROUP BY y.t;

r937 · February 23, 2012, 9:14pm

i was hoping for some real table and column names as well

i get ~really~ confused by stuff like this –

LEFT JOIN tbl_x y 
  LEFT OUTER JOIN tbl_y z

cms9651 · February 24, 2012, 7:39am

I don’t not know what to do… what other information is needed ?

r937 · February 24, 2012, 8:54am

please explain this –

FROM tbl_x x 
  LEFT JOIN tbl_x y 
  LEFT JOIN tbl_y z
    ON z.t = y.t
    ON y.A = x.A
   AND y.M = x.M -1 
   AND z.t = x.t

how am i supposed to know what’s going on there?

cms9651 · February 24, 2012, 5:51pm

I’d appreciate your help so very much.
I’m sorry I wrong the copy.

The query is:


SELECT
       COUNT(y.t) `n`
      ,y.t
      ,y.A
      ,y.M
      ,x.t
      ,x.A
      ,x.M
      ,z.dateField
      ,z.t
  FROM tbl_x x
  LEFT JOIN tbl_x y
  LEFT OUTER JOIN tbl_y z
    ON z.t = y.t
    ON y.A = x.A
   AND y.M = x.M + 1
   AND z.t = x.t
 WHERE 1
  AND (dateField BETWEEN MAKEDATE(YEAR(CURDATE())-1,1)
  AND ADDDATE(CURDATE(),INTERVAL - 1 YEAR))
  GROUP BY y.t;

With this statement make available the record with ‘dateField’ between 2011-01-01 and 2011-02-24 (today) and ‘M’ = 1 or ‘M’ = 2.


n	t	A	M	t	A	M	dateField	t
1	TOS1N	2011	2	TOS1N	2011	1	2011-02-22	TOS1N
1	TOS1Q	2011	2	TOS1Q	2011	1	2011-01-24	TOS1Q
1	TOS5P	2011	2	TOS5P	2011	1	2011-02-15	TOS5P

My problem is see in the output record when count ‘n’ is 0.
I think of LEFT OUTER JOIN but…

DaveMaxwell · February 24, 2012, 6:20pm

It’s this portion below causing you to only show where there’s data as that field is coming from the “z” table, but it’s requiring the condition to be true. If you want to have ones where the situation doesn’t match, you’ll need to add it to the join conditions instead of in the where clause.

AND (dateField BETWEEN MAKEDATE(YEAR(CURDATE())-1,1)

To be honest, that whole query doesn’t make logical sense. You’ve got all three instances of t in the return, but they all have to be the same, based on the query provided below. The group by will never work because you’ve got more fields in the select than in the group by, and I’ve never been ablt to get that to work (which is a good thing)

I don’t know if you’ve deliberately made the table names and field generic to hide the purpose, or if that’s your actual structure, but it’s far too generic for us to give you any real guidance. If that’s the real structure, I’d advise you to rethink it, because 6 months down the road, you’ll have no idea what fields x, t and M are. If you were trying to obfuscate it, just doing the table names would have been enough, and we would have been able to help you faster.

r937 · February 24, 2012, 6:28pm

quoted for truth

i’m still hoping to get an explanation for this –

FROM tbl_x x 
  LEFT JOIN tbl_x y 
  LEFT OUTER JOIN tbl_y z
    ON z.t = y.t
    ON y.A = x.A
   AND y.M = x.M + 1 
   AND z.t = x.t

cms9651 · February 24, 2012, 6:50pm

I don’t understand why you have this idea… the tables are 100% real and fields names… the fields are strange because they come from processing xml file…

The field ‘A’ is previous year school
The field ‘t’ is a alphanumeric code (identify one group of students)
The field ‘M’ is a month year school

Why think false data?

I need extract from join tbl_x and tbl_y the group of students who have completed evidence from 2011-01-01 and 2011-02-24 (made a test).
And I use join tbl_x vs. tbl_x for next month compared to January.
This happens day to day. Tomorrow will be from 2011-01-01 and 2011-02-25 (today a year ago).

I try this statement:


SELECT 
       COUNT(y.t) `n`
      ,y.t
      ,y.A
      ,y.M
      ,x.t
      ,x.A
      ,x.M
      ,z.dateField
      ,z.t 
  FROM tbl_x x 
  LEFT JOIN tbl_x y 
  LEFT OUTER JOIN tbl_y z
    ON z.t = y.t
    AND (dateField BETWEEN MAKEDATE(YEAR(CURDATE())-1,1) AND ADDDATE(CURDATE(),INTERVAL - 1 YEAR))
    ON y.A = x.A
   AND y.M = x.M + 1 
   AND z.t = x.t
 WHERE 1
  GROUP BY y.t;

Output:


n	t	A	M	t1	A1	M1	dateField	t2
0				CMP4M	2011	1			
1	TOS1N	2011	2	TOS1N	2011	1	2011-02-22	TOS1N
1	TOS1Q	2011	2	TOS1Q	2011	1	2011-01-24	TOS1Q
1	TOS5P	2011	2	TOS5P	2011	1	2011-02-15	TOS5P

r937 · February 24, 2012, 7:13pm

because real tables use more descriptive column names, like “previous_year_school”

also, because of this –

CREATE TABLE `tbl_y` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dateField` date DEFAULT NULL,
  `t` [COLOR="#FF0000"][B]varchar(4)[/B][/COLOR] DEFAULT NULL,
  UNIQUE KEY `id` (`id`),
  KEY `t` (`t`)
) ENGINE=MyISAM AUTO_INCREMENT=162 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of tbl_y
-- ----------------------------
INSERT INTO `tbl_y` VALUES ('1', '2011-12-21', [COLOR="#FF0000"]'TOS1M'[/COLOR]);
INSERT INTO `tbl_y` VALUES ('2', '2010-01-20', [COLOR="#FF0000"]'TOS5P'[/COLOR]);
INSERT INTO `tbl_y` VALUES ('3', '2010-01-18', [COLOR="#FF0000"]'SAR1M'[/COLOR]);
INSERT INTO `tbl_y` VALUES ('4', '2010-08-09', [COLOR="#FF0000"]'SAR1M'[/COLOR]);

finally, we are getting some real information

DaveMaxwell · February 24, 2012, 7:21pm

We’re not questioning whether it’s false data, but it doesn’t make sense to us because there was no explanation. Though, I re-iterate - you’ll want to document the mappings because in six months or a year, you’re not going to remember what they mean. I personally would rename the table fields and just change the XML import to map to the correct fields.

But IF I understand you, the query structure you need would be something like this:


SELECT Month1.t AS studentCode
  , Month1.A AS previousSchoolYear
  , Month1.M AS monthSchoolYear
     , Student.dateField
     , COUNT(Month1.t) Month1Tests
     , COUNT(Month2.t) Month2Test
  FROM tbl_x currentMonth 
  LEFT JOIN tbl_x Month2 ON (Month2.M = Month1.M + 1 AND Month2.A = Month1.A AND Month2.t = Student.t)
  LEFT OUTER JOIN tbl_y Student ON Month2.t = Student.t
 WHERE Month1.dateField BETWEEN MAKEDATE(YEAR(CURDATE())-1,1) AND ADDDATE(CURDATE(),INTERVAL - 1 YEAR))
 GROUP BY Month1.t, Month1.A, Month1.M

cms9651 · February 24, 2012, 8:03pm

I’d appreciate your help so very much.

Rudy is right: I have update the table in db mysql (t field varchar from 4 to 5) but not update the export in this thread… I’m sorry;
The names of fields in the tables mysql are the same names from xml file;
Now I use more descriptive column names.

I try your suggestion I will inform.
thanks

cms9651 · February 24, 2012, 8:57pm

Well… I’m sorry my friends… I have update XML file, tables mysql and the statement but the output not change…

SELECT
    Month1.students AS studentCode
  , Month1.previous_year_school AS previousSchoolYear
  , Month1.month_year_school AS monthSchoolYear
     , Student.dateField
     , COUNT(Month1.students) Month1Tests
     , COUNT(Month2.students) Month2Test
  FROM tbl_x Month1
  LEFT JOIN tbl_x Month2 ON (Month2.month_year_school = Month1.month_year_school + 1
                         AND Month2.previous_year_school = Month1.previous_year_school
                         AND Month2.students = Month1.students)
  LEFT OUTER JOIN tbl_y Student ON Month2.students = Student.students
 WHERE Student.dateField BETWEEN MAKEDATE(YEAR(CURDATE())-1,1) AND ADDDATE(CURDATE(),INTERVAL - 1 YEAR)
 GROUP BY Month1.students, Month1.previous_year_school, Month1.month_year_school;



studentCode	previousSchoolYear	monthSchoolYear	dateField	Month1Tests	Month2Test
TOS1N		2011			1		2011-02-22	1		1
TOS1Q		2011			1		2011-01-24	1		1
TOS5P		2011			1		2011-02-15	1		1



DROP TABLE IF EXISTS `tbl_x`;
CREATE TABLE `tbl_x` (
  `students` varchar(5) DEFAULT NULL,
  `month_year_school` int(1) DEFAULT NULL,
  `previous_year_school` int(4) DEFAULT NULL,
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;

-- ----------------------------
-- Records of tbl_x
-- ----------------------------
INSERT INTO `tbl_x` VALUES ('CMP4M', '1', '2011', '1');
INSERT INTO `tbl_x` VALUES ('CMP4N', '1', '2011', '2');
INSERT INTO `tbl_x` VALUES ('CMP4O', '1', '2011', '3');
INSERT INTO `tbl_x` VALUES ('CMP4P', '1', '2011', '4');
INSERT INTO `tbl_x` VALUES ('CMP6M', '1', '2011', '5');
INSERT INTO `tbl_x` VALUES ('CMP6N', '1', '2011', '6');
INSERT INTO `tbl_x` VALUES ('CMP6O', '1', '2011', '7');
INSERT INTO `tbl_x` VALUES ('CMP6P', '1', '2011', '8');
INSERT INTO `tbl_x` VALUES ('CMPSR', '1', '2011', '9');
INSERT INTO `tbl_x` VALUES ('CMPER', '1', '2011', '10');
INSERT INTO `tbl_x` VALUES ('CMPVM', '1', '2011', '11');
INSERT INTO `tbl_x` VALUES ('CMPSA', '1', '2011', '12');
INSERT INTO `tbl_x` VALUES ('CMP4M', '2', '2011', '13');
INSERT INTO `tbl_x` VALUES ('CMP4N', '2', '2011', '14');
INSERT INTO `tbl_x` VALUES ('CMP4O', '2', '2011', '15');
INSERT INTO `tbl_x` VALUES ('CMP4P', '2', '2011', '16');
INSERT INTO `tbl_x` VALUES ('CMP6M', '2', '2011', '17');
INSERT INTO `tbl_x` VALUES ('CMP6N', '2', '2011', '18');
INSERT INTO `tbl_x` VALUES ('CMP6O', '2', '2011', '19');
INSERT INTO `tbl_x` VALUES ('CMP6P', '2', '2011', '20');
INSERT INTO `tbl_x` VALUES ('CMPSR', '2', '2011', '21');
INSERT INTO `tbl_x` VALUES ('CMPER', '2', '2011', '22');
INSERT INTO `tbl_x` VALUES ('CMPVM', '2', '2011', '23');
INSERT INTO `tbl_x` VALUES ('CMPSA', '2', '2011', '24');
INSERT INTO `tbl_x` VALUES ('TOS1M', '1', '2011', '25');
INSERT INTO `tbl_x` VALUES ('TOS1N', '1', '2011', '26');
INSERT INTO `tbl_x` VALUES ('TOS1O', '1', '2011', '27');
INSERT INTO `tbl_x` VALUES ('TOS1P', '1', '2011', '28');
INSERT INTO `tbl_x` VALUES ('TOS1Q', '1', '2011', '29');
INSERT INTO `tbl_x` VALUES ('TOS5M', '1', '2011', '30');
INSERT INTO `tbl_x` VALUES ('TOS5N', '1', '2011', '31');
INSERT INTO `tbl_x` VALUES ('TOS5P', '1', '2011', '32');
INSERT INTO `tbl_x` VALUES ('TOS5Q', '1', '2011', '33');
INSERT INTO `tbl_x` VALUES ('TOS5R', '1', '2011', '34');
INSERT INTO `tbl_x` VALUES ('TOS6M', '1', '2011', '35');
INSERT INTO `tbl_x` VALUES ('TOS6N', '1', '2011', '36');
INSERT INTO `tbl_x` VALUES ('TOS6O', '1', '2011', '37');
INSERT INTO `tbl_x` VALUES ('TOSSR', '1', '2011', '38');
INSERT INTO `tbl_x` VALUES ('TOSER', '1', '2011', '39');
INSERT INTO `tbl_x` VALUES ('TOSVM', '1', '2011', '40');
INSERT INTO `tbl_x` VALUES ('TOSSA', '1', '2011', '41');
INSERT INTO `tbl_x` VALUES ('TOS1M', '2', '2011', '42');
INSERT INTO `tbl_x` VALUES ('TOS1N', '2', '2011', '43');
INSERT INTO `tbl_x` VALUES ('TOS1O', '2', '2011', '44');
INSERT INTO `tbl_x` VALUES ('TOS1P', '2', '2011', '45');
INSERT INTO `tbl_x` VALUES ('TOS1Q', '2', '2011', '46');
INSERT INTO `tbl_x` VALUES ('TOS5M', '2', '2011', '47');
INSERT INTO `tbl_x` VALUES ('TOS5N', '2', '2011', '48');
INSERT INTO `tbl_x` VALUES ('TOS5P', '2', '2011', '49');
INSERT INTO `tbl_x` VALUES ('TOS5Q', '2', '2011', '50');
INSERT INTO `tbl_x` VALUES ('TOS5R', '2', '2011', '51');
INSERT INTO `tbl_x` VALUES ('TOS6M', '2', '2011', '52');
INSERT INTO `tbl_x` VALUES ('TOS6N', '2', '2011', '53');
INSERT INTO `tbl_x` VALUES ('TOS6O', '2', '2011', '54');
INSERT INTO `tbl_x` VALUES ('TOSSR', '2', '2011', '55');
INSERT INTO `tbl_x` VALUES ('TOSER', '2', '2011', '56');
INSERT INTO `tbl_x` VALUES ('TOSVM', '2', '2011', '57');
INSERT INTO `tbl_x` VALUES ('TOSSA', '2', '2011', '58');


DROP TABLE IF EXISTS `tbl_y`;
CREATE TABLE `tbl_y` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dateField` date DEFAULT NULL,
  `students` varchar(5) DEFAULT NULL,
  UNIQUE KEY `id` (`id`),
  KEY `t` (`students`)
) ENGINE=MyISAM AUTO_INCREMENT=162 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of tbl_y
-- ----------------------------
INSERT INTO `tbl_y` VALUES ('1', '2011-12-21', 'TOS1M');
INSERT INTO `tbl_y` VALUES ('2', '2010-01-20', 'TOS5P');
INSERT INTO `tbl_y` VALUES ('3', '2010-01-18', 'SAR1M');
INSERT INTO `tbl_y` VALUES ('4', '2010-08-09', 'SAR1M');
INSERT INTO `tbl_y` VALUES ('5', '2010-01-10', 'LAZ7O');
INSERT INTO `tbl_y` VALUES ('6', '2011-12-13', 'TOS6O');
INSERT INTO `tbl_y` VALUES ('7', '2010-07-01', 'TOS5M');
INSERT INTO `tbl_y` VALUES ('8', '2007-12-12', 'TOS6M');
INSERT INTO `tbl_y` VALUES ('9', '2010-06-06', 'CMP6N');
INSERT INTO `tbl_y` VALUES ('10', '2010-07-18', 'CMP6O');
INSERT INTO `tbl_y` VALUES ('11', '2011-02-22', 'TOS1N');
INSERT INTO `tbl_y` VALUES ('12', '2008-06-16', 'LAZ00');
INSERT INTO `tbl_y` VALUES ('13', '2008-08-07', 'LAZ4M');
INSERT INTO `tbl_y` VALUES ('14', '2010-04-16', 'CMP6M');
INSERT INTO `tbl_y` VALUES ('15', '2010-05-20', 'CMP4P');
INSERT INTO `tbl_y` VALUES ('16', '2009-12-18', 'TOS5N');
INSERT INTO `tbl_y` VALUES ('17', '2011-12-19', 'TOS1N');
INSERT INTO `tbl_y` VALUES ('18', '2010-12-27', 'LAZ4M');
INSERT INTO `tbl_y` VALUES ('19', '2006-09-18', 'LAZ6M');
INSERT INTO `tbl_y` VALUES ('20', '2011-03-01', 'TOS6O');
INSERT INTO `tbl_y` VALUES ('21', '2011-06-23', 'SAR1N');
INSERT INTO `tbl_y` VALUES ('22', '2011-02-26', 'TOS5N');
INSERT INTO `tbl_y` VALUES ('23', '2010-05-07', 'CMP4P');
INSERT INTO `tbl_y` VALUES ('24', '2009-05-13', 'LAZ4P');
INSERT INTO `tbl_y` VALUES ('25', '2008-10-15', 'SAR1O');
INSERT INTO `tbl_y` VALUES ('26', '2010-01-12', 'SAR1Q');
INSERT INTO `tbl_y` VALUES ('27', '2010-09-07', 'TOS00');
INSERT INTO `tbl_y` VALUES ('28', '2010-09-07', 'TOS00');
INSERT INTO `tbl_y` VALUES ('29', '2009-11-06', 'LAZ6N');
INSERT INTO `tbl_y` VALUES ('30', '2011-08-19', 'CMP6M');
INSERT INTO `tbl_y` VALUES ('31', '2010-10-20', 'TOS1M');
INSERT INTO `tbl_y` VALUES ('32', '2010-04-14', 'TOS5M');
INSERT INTO `tbl_y` VALUES ('33', '2010-12-02', 'TOS5M');
INSERT INTO `tbl_y` VALUES ('34', '2009-09-16', 'CMP6O');
INSERT INTO `tbl_y` VALUES ('35', '2010-02-10', 'CMP6P');
INSERT INTO `tbl_y` VALUES ('36', '2009-06-03', 'CMP4N');
INSERT INTO `tbl_y` VALUES ('37', '2010-03-22', 'TOS1Q');
INSERT INTO `tbl_y` VALUES ('38', '2010-08-04', 'TOS1Q');
INSERT INTO `tbl_y` VALUES ('39', '2010-09-06', 'SAR1M');
INSERT INTO `tbl_y` VALUES ('40', '2011-07-06', 'CMP6M');
INSERT INTO `tbl_y` VALUES ('41', '2011-07-06', 'CMP6M');
INSERT INTO `tbl_y` VALUES ('42', '2011-07-11', 'CMP6P');
INSERT INTO `tbl_y` VALUES ('43', '2010-07-30', 'CMP6M');
INSERT INTO `tbl_y` VALUES ('44', '2010-04-23', 'SAR00');
INSERT INTO `tbl_y` VALUES ('45', '2010-11-08', 'SAR00');
INSERT INTO `tbl_y` VALUES ('46', '2010-01-21', 'TOS1N');
INSERT INTO `tbl_y` VALUES ('47', '2010-01-04', 'TOS1O');
INSERT INTO `tbl_y` VALUES ('48', '2010-12-14', 'TOS1O');
INSERT INTO `tbl_y` VALUES ('49', '2011-03-15', 'TOS1P');
INSERT INTO `tbl_y` VALUES ('50', '2011-01-24', 'TOS1Q');
INSERT INTO `tbl_y` VALUES ('51', '2011-11-10', 'TOS5M');
INSERT INTO `tbl_y` VALUES ('52', '2011-05-05', 'TOS5Q');
INSERT INTO `tbl_y` VALUES ('53', '2011-07-07', 'TOS6O');
INSERT INTO `tbl_y` VALUES ('54', '2010-11-15', 'LAZ2M');
INSERT INTO `tbl_y` VALUES ('55', '2010-05-14', 'LAZ2N');
INSERT INTO `tbl_y` VALUES ('56', '2011-09-09', 'LAZ2N');
INSERT INTO `tbl_y` VALUES ('57', '2010-11-12', 'LAZ4M');
INSERT INTO `tbl_y` VALUES ('58', '2010-11-12', 'LAZ4M');
INSERT INTO `tbl_y` VALUES ('59', '2011-06-08', 'LAZ4M');
INSERT INTO `tbl_y` VALUES ('60', '2011-08-08', 'LAZ4O');
INSERT INTO `tbl_y` VALUES ('61', '2011-05-25', 'LAZ4P');
INSERT INTO `tbl_y` VALUES ('62', '2009-09-21', 'LAZ4Q');
INSERT INTO `tbl_y` VALUES ('63', '2010-09-18', 'LAZ4Q');
INSERT INTO `tbl_y` VALUES ('64', '2011-02-08', 'LAZ4Q');
INSERT INTO `tbl_y` VALUES ('65', '2010-01-21', 'LAZ7M');
INSERT INTO `tbl_y` VALUES ('66', '2010-12-20', 'LAZ7M');
INSERT INTO `tbl_y` VALUES ('67', '2011-12-13', 'LAZ7O');
INSERT INTO `tbl_y` VALUES ('68', '2010-03-11', 'CMP4M');
INSERT INTO `tbl_y` VALUES ('69', '2010-03-11', 'CMP4M');
INSERT INTO `tbl_y` VALUES ('70', '2011-04-04', 'CMP4P');
INSERT INTO `tbl_y` VALUES ('71', '2011-07-13', 'SAR1O');
INSERT INTO `tbl_y` VALUES ('72', '2010-03-04', 'LAZ4N');
INSERT INTO `tbl_y` VALUES ('73', '2009-06-18', 'LAZ7N');
INSERT INTO `tbl_y` VALUES ('74', '2011-03-09', 'SAR1N');
INSERT INTO `tbl_y` VALUES ('75', '2011-08-08', 'LAZ00');
INSERT INTO `tbl_y` VALUES ('76', '2011-08-20', 'TOS1N');
INSERT INTO `tbl_y` VALUES ('77', '2010-11-18', 'TOS5P');
INSERT INTO `tbl_y` VALUES ('78', '2011-08-23', 'TOS6M');
INSERT INTO `tbl_y` VALUES ('79', '2011-05-19', 'LAZ7P');
INSERT INTO `tbl_y` VALUES ('80', '2010-05-20', 'SAR1Q');
INSERT INTO `tbl_y` VALUES ('81', '2011-09-05', 'TOS1Q');
INSERT INTO `tbl_y` VALUES ('82', '2011-05-23', 'TOS5M');
INSERT INTO `tbl_y` VALUES ('83', '2011-06-27', 'TOS5N');
INSERT INTO `tbl_y` VALUES ('84', '2011-02-23', 'LAZ00');
INSERT INTO `tbl_y` VALUES ('85', '2011-08-30', 'LAZ6N');
INSERT INTO `tbl_y` VALUES ('86', '2010-04-14', 'SAR1Q');
INSERT INTO `tbl_y` VALUES ('87', '2011-03-13', 'SAR1M');
INSERT INTO `tbl_y` VALUES ('88', '2011-03-05', 'SAR1P');
INSERT INTO `tbl_y` VALUES ('89', '2011-08-10', 'SAR1Q');
INSERT INTO `tbl_y` VALUES ('90', '2011-10-03', 'TOS00');
INSERT INTO `tbl_y` VALUES ('91', '2011-10-03', 'TOS00');
INSERT INTO `tbl_y` VALUES ('92', '2010-02-19', 'CMP4M');
INSERT INTO `tbl_y` VALUES ('93', '2009-06-18', 'CMP4O');
INSERT INTO `tbl_y` VALUES ('94', '2009-04-18', 'TOS1M');
INSERT INTO `tbl_y` VALUES ('95', '2010-03-05', 'TOS00');
INSERT INTO `tbl_y` VALUES ('96', '2010-03-05', 'TOS00');
INSERT INTO `tbl_y` VALUES ('97', '2011-03-20', 'SAR1M');
INSERT INTO `tbl_y` VALUES ('98', '2009-07-22', 'LAZ2M');
INSERT INTO `tbl_y` VALUES ('99', '2010-03-18', 'TOS1O');
INSERT INTO `tbl_y` VALUES ('100', '2011-12-22', 'TOS1Q');
INSERT INTO `tbl_y` VALUES ('101', '2011-02-15', 'TOS5P');
INSERT INTO `tbl_y` VALUES ('102', '2010-07-29', 'TOS5R');
INSERT INTO `tbl_y` VALUES ('103', '2011-06-13', 'TOS5R');
INSERT INTO `tbl_y` VALUES ('104', '2011-02-24', 'LAZ4P');
INSERT INTO `tbl_y` VALUES ('105', '2010-09-21', 'LAZ4N');
INSERT INTO `tbl_y` VALUES ('106', '2010-05-24', 'LAZ7M');
INSERT INTO `tbl_y` VALUES ('107', '2009-11-04', 'TOS1P');
INSERT INTO `tbl_y` VALUES ('108', '2008-05-06', 'TOS5Q');
INSERT INTO `tbl_y` VALUES ('109', '2009-11-20', 'TOS5R');
INSERT INTO `tbl_y` VALUES ('110', '2007-05-30', 'TOS6N');
INSERT INTO `tbl_y` VALUES ('111', '2009-11-19', 'TOS6O');
INSERT INTO `tbl_y` VALUES ('112', '2011-04-12', 'CMP4P');
INSERT INTO `tbl_y` VALUES ('113', '2009-03-23', 'LAZ4O');
INSERT INTO `tbl_y` VALUES ('114', '2010-03-11', 'LAZ7P');
INSERT INTO `tbl_y` VALUES ('115', '2011-09-21', 'LAZ4O');
INSERT INTO `tbl_y` VALUES ('116', '2009-08-05', 'CMP00');
INSERT INTO `tbl_y` VALUES ('117', '2011-10-04', 'TOS1Q');
INSERT INTO `tbl_y` VALUES ('118', '2009-06-09', 'SAR1N');
INSERT INTO `tbl_y` VALUES ('119', '2009-09-23', 'SAR1P');
INSERT INTO `tbl_y` VALUES ('155', '2011-08-22', 'CMP6O');
INSERT INTO `tbl_y` VALUES ('156', '2011-09-06', 'CMP4M');
INSERT INTO `tbl_y` VALUES ('157', '2011-09-26', 'CMP6O');
INSERT INTO `tbl_y` VALUES ('158', '2011-10-16', 'CMP6M');
INSERT INTO `tbl_y` VALUES ('159', '2011-11-30', 'CMP6P');
INSERT INTO `tbl_y` VALUES ('160', '2011-11-30', 'CMP6O');
INSERT INTO `tbl_y` VALUES ('161', '2011-12-12', 'CMP00');

DaveMaxwell · February 27, 2012, 2:44pm

Sorry I didn’t get back to this thread until now…

The reason you’re results haven’t changed is because of the WHERE clause. As soon as you put a requirement on a portion of the left outer joined table, it’s no essentially longer an outer join - it technically still is, but in reality it’s an inner join because only those record which meet the where clause are accepted, which means those records that don’t match are ignored.

The problem you’re going to run into is there’s no dates on the tbl_x table, so there’s no real way to limit those out that I can see, but to get those record regardless of whether it’s true or not, move the where clause to the join:


SELECT 
    Month1.students AS studentCode
  , Month1.previous_year_school AS previousSchoolYear
  , Month1.month_year_school AS monthSchoolYear
     , Student.dateField
     , COUNT(Month1.students) Month1Tests
     , COUNT(Month2.students) Month2Test
  FROM tbl_x Month1
  LEFT JOIN tbl_x Month2 ON (Month2.month_year_school = Month1.month_year_school + 1 
                         AND Month2.previous_year_school = Month1.previous_year_school
                         AND Month2.students = Month1.students)
  LEFT OUTER JOIN tbl_y Student ON Month2.students = Student.students
        AND Student.dateField BETWEEN MAKEDATE(YEAR(CURDATE())-1,1) AND ADDDATE(CURDATE(),INTERVAL - 1 YEAR)
 GROUP BY Month1.students, Month1.previous_year_school, Month1.month_year_school;

cms9651 · February 28, 2012, 10:14pm

thanks a lot for your help.

This statement produce this output.

Why I have for single group students two record?

I think one record for one group students… I don’t understand ?

studentCode	previousSchoolYear	monthSchoolYear	dateField	Month1Tests	Month2Test
CMP4M		2011			1				1		1
CMP4M		2011			2				1		0
CMP4N		2011			1				1		1
CMP4N		2011			2				1		0
CMP4O		2011			1				1		1
CMP4O		2011			2				1		0
CMP4P		2011			1				1		1
CMP4P		2011			2				1		0
CMP6M		2011			1				1		1
CMP6M		2011			2				1		0
CMP6N		2011			1				1		1
CMP6N		2011			2				1		0
CMP6O		2011			1				1		1
CMP6O		2011			2				1		0
CMP6P		2011			1				1		1
CMP6P		2011			2				1		0
CMPER		2011			1				1		1
CMPER		2011			2				1		0
CMPSA		2011			1				1		1
CMPSA		2011			2				1		0
CMPSR		2011			1				1		1
CMPSR		2011			2				1		0
CMPVM		2011			1				1		1
CMPVM		2011			2				1		0
TOS1M		2011			1				1		1
TOS1M		2011			2				1		0
TOS1N		2011			1		2011-02-22	1		1
TOS1N		2011			2				1		0
TOS1O		2011			1				1		1
TOS1O		2011			2				1		0
TOS1P		2011			1				1		1
TOS1P		2011			2				1		0
TOS1Q		2011			1		2011-01-24	1		1
TOS1Q		2011			2				1		0
TOS5M		2011			1				1		1
TOS5M		2011			2				1		0
TOS5N		2011			1		2011-02-26	1		1
TOS5N		2011			2				1		0
TOS5P		2011			1		2011-02-15	1		1
TOS5P		2011			2				1		0
TOS5Q		2011			1				1		1
TOS5Q		2011			2				1		0
TOS5R		2011			1				1		1
TOS5R		2011			2				1		0
TOS6M		2011			1				1		1
TOS6M		2011			2				1		0
TOS6N		2011			1				1		1
TOS6N		2011			2				1		0
TOS6O		2011			1				1		1
TOS6O		2011			2				1		0
TOSER		2011			1				1		1
TOSER		2011			2				1		0
TOSSA		2011			1				1		1
TOSSA		2011			2				1		0
TOSSR		2011			1				1		1
TOSSR		2011			2				1		0
TOSVM		2011			1				1		1
TOSVM		2011			2				1		0
TOS1N		2011			1		2011-02-22	1		1
TOS1Q		2011			1		2011-01-24	1		1
TOS5N		2011			1		2011-02-26	1		1
TOS5P		2011			1		2011-02-15	1		1



[SQL] SELECT
    Month1.students AS studentCode
  , Month1.previous_year_school AS previousSchoolYear
  , Month1.month_year_school AS monthSchoolYear
     , Student.dateField
     , COUNT(Month1.students) Month1Tests
     , COUNT(Month2.students) Month2Test
  FROM tbl_x Month1
  LEFT JOIN tbl_x Month2 ON (Month2.month_year_school = Month1.month_year_school + 1
                         AND Month2.previous_year_school = Month1.previous_year_school
                         AND Month2.students = Month1.students)
  LEFT OUTER JOIN tbl_y Student ON Month2.students = Student.students
        AND Student.dateField BETWEEN MAKEDATE(YEAR(CURDATE())-1,1) AND ADDDATE(CURDATE(),INTERVAL - 1 YEAR)
 GROUP BY Month1.students, Month1.previous_year_school, Month1.month_year_school;
Affected rows: 0
Time: 0.000ms

DaveMaxwell · February 29, 2012, 4:52pm

That would be correct, as it counts one for month 1 and one for month 2, then also for the datefield if appropriate. If you want just a grouping by the student, then remove the month_year_school from the select and the group by.

cms9651 · March 2, 2012, 5:09pm

Thanks a lot-