WG: Query problem

you forgott

WHERE
(trr.Trainer_Code is not null);


/*
SELECT
trr.Trainer_ID,
trr.Trainer_Code,
atd.Trainer_Trainee_Code

FROM
attendance_detail atd

LEFT JOIN
trainer trr
ON
(trr.Trainer_Code = atd.Trainer_Trainee_Code)
AND
(atd.Attendance_Type = 'TRR')
WHERE
(trr.Trainer_Code is not null);
*/



SELECT
att.Attendance_Code,
trr.Trainer_ID,
crs.Course_ID,
COUNT(tre.Trainee_Code) AS 'Trainee_Attend'

FROM
attendance att

INNER JOIN
attendance_detail atd
ON
(atd.Attendance_Code = att.Attendance_Code)

LEFT JOIN
trainer trr
ON
(trr.Trainer_Code = atd.Trainer_Trainee_Code)
AND
(atd.Attendance_Type = 'TRR')

LEFT JOIN
trainee tre
ON
(tre.Trainee_Code = atd.Trainer_Trainee_Code)
AND
(atd.Attendance_Type = 'TRE')

LEFT JOIN
course crs
ON
(crs.Course_Code = att.Course_Code)

WHERE
att.Attendance_Date BETWEEN 20051101 AND 20051231
AND (trr.Trainer_Code is not null)

GROUP BY
crs.Course_ID;


----- Weitergeleitet von Thomas Treffehn/BROCHIER/DE am 18.11.2005 10:49
-----

ascll

An
18.11.2005 10:38 "win32@ lists.mysql.com"

Kopie

Thema
Query problem










Greetings,

Could you guys help me figure out what wrong with my
query and how do I get my EXPECTED results set ?

I'm using MySQL ver 4.1.15

Thanks in advance :-)


Scripts
=======
SELECT
att.Attendance_Code,
trr.Trainer_ID,
crs.Course_ID,
COUNT(tre.Trainee_Code) AS 'Trainee_Attend'

FROM
attendance att

INNER JOIN
attendance_detail atd
ON
(atd.Attendance_Code = att.Attendance_Code)

LEFT JOIN
trainer trr
ON
(trr.Trainer_Code = atd.Trainer_Trainee_Code)
AND
(atd.Attendance_Type = 'TRR')

LEFT JOIN
trainee tre
ON
(tre.Trainee_Code = atd.Trainer_Trainee_Code)
AND
(atd.Attendance_Type = 'TRE')

LEFT JOIN
course crs
ON
(crs.Course_Code = att.Course_Code)

WHERE
att.Attendance_Date BETWEEN 20051101 AND 20051231

GROUP BY
crs.Course_ID;



Actual Result Set
=================
Attendance_Code Trainer_ID Course_ID Trainee_Attend
------------------------------------------------------
37 (NULL) CHILDA2Z 2
39 KELVINWONG DELPHI2005 4
40 KELVINWONG MYSQL_PHP 3
42 (NULL) PHOTOSHOP 3
43 (NULL) PRITC 1



Expected Result Set
===================
Attendance_Code Trainer_ID Course_ID Trainee_Attend
------------------------------------------------------
37 ALEXMOK CHILDA2Z 2
39 KELVINWONG DELPHI2005 4
40 KELVINWONG MYSQL_PHP 3
42 KWAIYING PHOTOSHOP 3
43 KELVINWONG PRITC 1



Table Strustures
================
CREATE TABLE IF NOT EXISTS `trainer` (
`Trainer_Code` smallint unsigned NOT NULL
auto_increment,
`Trainer_ID` varchar(15) NOT NULL default '',
`Rate_Code` smallint unsigned NOT NULL,
PRIMARY KEY (`Trainer_Code`),
UNIQUE KEY (`Trainer_ID`),
KEY (`Rate_Code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `trainee` (
`Trainee_Code` mediumint unsigned NOT NULL
auto_increment,
`Trainee_ID` varchar(15) NOT NULL default '',
PRIMARY KEY (`Trainee_Code`),
UNIQUE KEY (`Trainee_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `course` (
`Course_Code` smallint unsigned NOT NULL
auto_increment,
`Course_ID` varchar(15) NOT NULL default '',
PRIMARY KEY (`Course_Code`),
UNIQUE KEY (`Course_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `attendance` (
`Attendance_Code` int unsigned NOT NULL
auto_increment,
`Attendance_Date` date NOT NULL,
`Course_Code` smallint unsigned NOT NULL default
'0',
PRIMARY KEY (`Attendance_Code`),
KEY (`Attendance_Date`,`Course_Code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `attendance_detail`;
CREATE TABLE IF NOT EXISTS `attendance_detail` (
`Attendance_Code` int unsigned NOT NULL default '0',
`Trainer_Trainee_Code` mediumint unsigned NOT NULL
default '0',
`Attendance_Type` varchar(3) default NULL,
PRIMARY KEY
(`Attendance_Code`,`Trainer_Trainee_Code`,`Attendance_Type`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;




__________________________________
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=treffehn@brochier.de



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org