<p>I have a table that lists students' test results. Each row contains a student ID, and there are columns, each column representing an individual test score. Now I need to create a query that will allow me to see a separate row for the student ID corresponding to each test score. I've searched Stack Overflow for the best approach, but I haven't found one that looks efficient. I wrote the code listed below for one student (1000002), but it seems overly complex for what I'm trying to achieve and only works for three test scores, so I need to add more code to get All 16 points. Is there an easy way to do this with a single query (preferred) or do I need to extract the rows and then use a loop to build an array from each element (second preferred)? </p> <pre class="brush:php;toolbar:false;">SELECT scores_uid , score1 FROM assessment_scores WHERE fk_assigned_uid = '1000002' UNION ALL SELECT scores_uid , score2 FROM assessment_scores WHERE fk_assigned_uid = '1000002' UNION ALL SELECT scores_uid , score3 FROM assessment_scores WHERE fk_assigned_uid = '1000002';</pre> <p><br /></p>

SELECT test_results.result AS Result,
 students.name as Student,
 tests.number as Testnumber 
FROM test_results 
LEFT JOIN students ON students.id = test_results.student_id
LEFT JOIN tests ON tests.id = test_results.test_id
WHERE student_id = {{id}}