listing of marks based on sample data, MySQL

Language: Layout:

DROP TABLE if exists evaluations_mark;
DROP TABLE if exists subject;
CREATE TABLE evaluations_mark (
  `mark_id` int(13) NOT NULL default 0,
  `note` varchar(225) NOT NULL,
  `comments` text,
  `subject_id` int(13) NOT NULL,
  `course_id` int(13) NOT NULL default 0,
  `user_id` int(13) NOT NULL default 0,
  `evaluation_id` int(13) NOT NULL default 0,
  `number_exam` int(2) NOT NULL default 0
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `subject` (
  `subject_id` int(6) auto_increment primary key,
  `name` varchar(50) NOT NULL,
  `course_id` int(6) NOT NULL default 0,
  `teacher_id` int(6) NOT NULL default 0,
  `load_type` varchar(20) NOT NULL default '' ,
  `hours` int(11) NOT NULL default 0,
  `area_id` int(2) NOT NULL default 0,
  `active` tinyint(1) NOT NULL default 0,
  `number_exam` int(6) NOT NULL default 4,
  `parent` int(5) NOT NULL default 0,
  `print_visible` tinyint(1) NOT NULL default 1
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO subject 
(subject_id, name, course_id, teacher_id,load_type, hours, area_id, active,
number_exam, parent,  print_visible) VALUES   
(80,'Lengua y Literatura' ,8,0,1,4,2,0,4,0,1),
(81,'Matemática'          ,8,0,1,4,2,0,4,0,1),
(82,'Ciencias Sociales'   ,8,0,1,4,2,0,4,0,1),
(83,'Ciencias Naturales'  ,8,0,1,4,2,0,4,0,1),
(84,'Italiano'            ,8,0,2,5,2,0,4,0,1),
(85,'Inglés'              ,8,0,2,4,2,0,4,0,1),
(86,'Expresion Plástica'  ,8,0,2,3,2,0,2,0,1),
(87,'Música'              ,8,0,2,2,2,0,2,0,1),
(88,'Informática'         ,8,0,2,3,2,0,2,0,1),
(89,'Educación Física'    ,8,0,2,2,2,0,2,0,1);

INSERT INTO evaluations_mark (mark_id, note, subject_id, course_id,  user_id, evaluation_id, number_exam) VALUES
(4363,'SA',  87, 8, 28265, 0, 1),
(4892,'MSA', 84, 8, 28265, 0, 2),
(1078,'SA',  85, 8, 28265, 0, 1),
(3646,'MSA', 88, 8, 28265, 0, 1),
(2634,'MSA', 89, 8, 28265, 0, 1),
( 125,'SA',  80, 8, 28265, 0, 1),
( 147,'MSA', 82, 8, 28265, 0, 1),
(1430,'MSA', 84, 8, 28265, 0, 1),
( 169,'MSA', 83, 8, 28265, 0, 1),
(3753,'SA',  80, 8, 28265, 0, 2),
(3510,'MSA', 85, 8, 28265, 0, 2),
( 191,'SA',  81, 8, 28265, 0, 1),
(3775,'SA',  81, 8, 28265, 0, 2),
(6858,'MSA', 86, 8, 28265, 0, 1),
(3279,'MSA', 83, 8, 28265, 0, 2),
(3797,'SA',  82, 8, 28265, 0, 2);

select * from subject;
select * from evaluations_mark;

SELECT subject.name as subject, GROUP_CONCAT(evaluations_mark.note) as mark 
FROM evaluations_mark 
LEFT JOIN subject ON evaluations_mark.subject_id = subject.subject_id
WHERE evaluations_mark.number_exam IN (1,2) AND evaluations_mark.user_id = 28265
AND subject.print_visible='1' 
AND subject.number_exam = '4' 
GROUP BY evaluations_mark.subject_id


absolute service time: 0,38 sec