mysql - taking one row from one table based on ID and DATE and get multiple rows in another table if not show null values

Question

I have a student table which has name,student_id,date_of_exam,result,.. and another result table which has student_id,test_name,mark test_name and marks will be multiple rows. number of test are dynamic.. it is not static.

I need table2.test_name as header and show marks in my report

table 1

| id | name | date_of_exam|result|
------------------------------------------------
|  1 | gg   | 24-08-2016  | pass |
|  2 | hh   | 24-08-2016  | pass |
|  3 | ee   | 25-08-2016  | abse |

table 2

| student_id | test_name |mark |
-------------------------------------------------
|  1 | test1 | 20 |
|  1 | test2 | 40 |
|  1 | test3 | 50 |
|  2 | test1 | 30 |
|  2 | test2 | 50 |

output should be

| id | name | date_of_exam|result| test1 | test2 |test3 |
---------------------------------------------------------
|  1 | gg   | 24-08-2016  | pass | 20    | 40    |   50 |
|  2 | hh   | 24-08-2016  | pass | 30    | 50    | NULL |
|  3 | ee   | 25-08-2016  | abse | NULL  | NULL  | NULL |

Answer 1

Try this using CASE and Grouping:

SELECT table1.id,MAX(table1.name),MAX(table1.date_of_exam),MAX(table1.result),
       MAX(CASE WHEN table2.test_name = 'test1' THEN table2.mark END) as test1,
       MAX(CASE WHEN table2.test_name = 'test2' THEN table2.mark END) as test2,
       MAX(CASE WHEN table2.test_name = 'test3' THEN table2.mark END) as test3
FROM table1
LEFT JOIN table2 ON (table1.id=table2.student_id)
GROUP BY table1.Id

SQLfiddle demo