|
I'm getting wrong results while querying using group_concat. Results are randomly inverted.
How to repeat:
CREATE TABLE `subject` (
|
`subject_id` int(6) NOT NULL,
|
`name` varchar(50) NOT NULL,
|
`course_id` int(6) NOT NULL,
|
`teacher_id` int(6) NOT NULL,
|
`load_type` varchar(20) NOT NULL,
|
`hours` int(11) NOT NULL,
|
`area_id` int(2) NOT NULL,
|
`active` tinyint(1) NOT NULL,
|
`number_exam` int(6) NOT NULL,
|
`parent` int(5) NOT NULL,
|
`print_visible` tinyint(1) NOT NULL
|
) 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);
|
|
CREATE TABLE `evaluations_mark` (
|
`mark_id` int(13) NOT NULL,
|
`note` varchar(225) NOT NULL,
|
`comments` text NOT NULL,
|
`subject_id` int(13) NOT NULL,
|
`course_id` int(13) NOT NULL,
|
`user_id` int(13) NOT NULL,
|
`evaluation_id` int(13) NOT NULL,
|
`number_exam` int(2) NOT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
INSERT INTO `evaluations_mark` (`mark_id`, `note`, `comments`, `subject_id`, `course_id`, `user_id`, `evaluation_id`, `number_exam`) VALUES
|
(125, 'SA', '', 80, 8, 28265, 0, 1),
|
(147, 'MSA', '', 82, 8, 28265, 0, 1),
|
(169, 'MSA', '', 83, 8, 28265, 0, 1),
|
(191, 'SA', '', 81, 8, 28265, 0, 1),
|
(1078, 'SA', '', 85, 8, 28265, 0, 1),
|
(1430, 'MSA', '', 84, 8, 28265, 0, 1),
|
(2634, 'MSA', '', 89, 8, 28265, 0, 1),
|
(3279, 'MSA', '', 83, 8, 28265, 0, 2),
|
(3510, 'MSA', '', 85, 8, 28265, 0, 2),
|
(3646, 'MSA', '', 88, 8, 28265, 0, 1),
|
(3753, 'SA', '', 80, 8, 28265, 0, 2),
|
(3775, 'SA', '', 81, 8, 28265, 0, 2),
|
(3797, 'SA', '', 82, 8, 28265, 0, 2),
|
(4363, 'SA', '', 87, 8, 28265, 0, 1),
|
(4892, 'MSA', '', 84, 8, 28265, 0, 2),
|
(6858, 'MSA', '', 86, 8, 28265, 0, 1);
|
|
ALTER TABLE `evaluations_mark`
|
ADD PRIMARY KEY (`mark_id`);
|
if I run this:
SELECT subject.name as subject, GROUP_CONCAT(evaluations_mark.note order by subject.name asc) 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
|
ORDER by subject.name ASC
|
I should get this result:
subject mark
|
Ciencias Naturales MSA,MSA
|
Ciencias Sociales MSA,MSA
|
Inglés MSA,SA
|
Italiano MSA,MSA
|
Lengua y Literatura MSA,SA
|
Matemática SA,SA
|
However, I get this:
subject mark
|
Ciencias Naturales MSA,MSA
|
Ciencias Sociales SA,MSA
|
Inglés MSA,SA
|
Italiano MSA,MSA
|
Lengua y Literatura SA,SA
|
Matemática SA,SA
|
If I run same thing in here: http://sqlfiddle.com/#!9/d8b52e/6.
Result is correct.
If I run it under phpmyadmin/mysql, result is wrong.
Why?
|