Click here to Skip to main content
13,800,574 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
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:
Posted 4-Nov-18 11:49am
Updated 6-Dec-18 11:47am
v3
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Aah, the Pivot Table.

For MySql, you would use the Group_Concat() instruction to do this. The following article demos this functionality along with other RDBMS versions
http://www.artfulsoftware.com/infotree/qrytip.php?id=78[^]
  Permalink  
Comments
ammarsoftware 7-Dec-18 9:34am
   
Thank you
You have already tried to use the sentence
But it does not do the trick because it gives random answers:

---------------------
select a.code_student, b.name, group_concat(a.mark SEPARATOR ' ') as student_enrollment from tb_marks a, tb_student_old b where a.code_student = b.id group by a.code_student, b.name
MadMyche 7-Dec-18 9:54am
   
The data you are inserting into the tables does not correspond to the results you desire; your tb_marks insert neither contains data for Ahmad nor any marks of 88.
ammarsoftware 7-Dec-18 10:55am
   
you can help me in anydisk?
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

  Permalink  
Comments
ammarsoftware 5-Nov-18 15:08pm
   
i used the statement :
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:

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web03 | 2.8.181215.1 | Last Updated 6 Dec 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100