[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql-win32
Subject:    WG: Query problem
From:       treffehn () brochier ! de
Date:       2005-11-18 13:58:22
Message-ID: OF4001F08F.FD9E1654-ONC12570BD.004C8409-C12570BD.004CC291 () brochier ! de
[Download RAW message or body]





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                                                         
             <ascll@yahoo.com>                                             
                                                                        An 
             18.11.2005 10:38           "win32@ lists.mysql.com"           
                                        <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=mysql-win32@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]