CODEWITHSUNDEEP
mysql
Marielle D.
Marielle D.

Reputation: 3

Placing all the grades of student1

I have a table subject which has columns subject code, user_id and grade. Also I have a table for students.

Student table:

| user_id | name |
+---------+------+
| 17000   | elle |

Subject table:

| subjcode | user_id | grade |
+----------+---------+-------+
| os1      | 17000   | 90    |
| micro    | 17000   | 90    |

What is the right query so that the output will look like this?

| user_id | os1 | micro1 |
+---------+-----+--------+
| 17000   | 90  | 90     |

Upvotes: 0

Views: 72

Answers (2)

Ruben_PH
Ruben_PH

Reputation: 1812

Based from my own question before SQL Query fields as columns, your code would look like something like this:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(case when subjcode = ''',
      subjcode,
      ''' then grade end) AS ',
      subjcode
    )
  ) INTO @sql
FROM subject_table;

SET @sql = CONCAT('SELECT s.name, ', @sql, ' 
                   FROM student_table s
                   LEFT JOIN subject_table AS g 
                    ON s.user_id = g.user_id
                   GROUP BY s.name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Upvotes: 0

Jacques Amar
Jacques Amar

Reputation: 1833

This requires you to combine results from multiple line in one line, which is not easily handled by a SQL query. Plus you cannot get a variable number of fields as a results (what if there are 5 subject matters). This is best handled at the display level with a proper language.

Of course a stored procedure will do it but that's a whole other subject :-)

Upvotes: 1

Related Questions