Hi
I have trouble displaying students' grades in one table
The table data is as follows:
CREATE TABLE `tb_marks` (
`id` int(11) NOT NULL,
`id_subject` int(11) NOT NULL,
`code_student` int(11) NOT NULL,
`round` varchar(50) NOT NULL,
`mark` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
------------------
INSERT INTO `tb_marks` (`id`, `id_subject`, `code_student`, `round`, `mark`) VALUES
(3, 1, 2, 'first', '45'),
(2, 1, 3, 'first', '45'),
(4, 2, 2, 'first', '46'),
(5, 3, 2, 'first', '0'),
(6, 4, 2, 'first', '84'),
(7, 5, 2, 'first', '77'),
(8, 6, 2, 'first', '65');
---------------------------------
CREATE TABLE `tb_student_old` (
`id` int(11) NOT NULL,
`name` varchar(100) NOT NULL,
`school` varchar(50) NOT NULL,
`code` int(11) NOT NULL,
`age` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-------------------
INSERT INTO `tb_student_old` (`id`, `name`, `school`, `code`, `age`) VALUES
(1, 'ahmad', 'school', 0, NULL),
(2, 'noor', 'school', 0, NULL),
(3, 'salim', 'school', 0, NULL),
(4, 'emad', 'school', 0, NULL);
------------------------------------------
CREATE TABLE `tb_subject` (
`id` int(11) NOT NULL,
`name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
------------------
INSERT INTO `tb_subject` (`id`, `name`) VALUES
(1, 'islamic'),
(2, 'arabic'),
(3, 'english'),
(4, 'math'),
(5, 'science'),
(6, 'social'),
(7, 'geographically'),
(8, 'date'),
(9, 'national');
I need the query or programming that gives me the table as follows:
SNo Student Name islamic|arabic|english|math|science|social|geographic|Total
1 ahmad 88 88 88 88 88 88 88 8888
What I have tried:
SELECT `tb_marks`.`id` , `tb_marks`.`round`, `tb_marks`.`mark` , `tb_subject`.`name` , `tb_student_old`.`name`, `tb_student_old`.`school` FROM `tb_marks` INNER JOIN `tb_subject` ON `tb_subject`.`id` = `id_subject` INNER JOIN `tb_student_old` ON `tb_student_old`.`id` = `code_student` WHERE code_student='2';
But the result was not right: