Retrieve one column with different values using foreign key

Question

I wanted to select one column horizontally with different values using foreign key. This foreign key reference from another table name gradingperiod_id.

first | second
   80      81

grades:

grade_id | grade | gradingperiod_id 
  1          80         7001
  2          81         7002

gradeperiod_mt:

gradeperiod_id | period
     7001           1
     7002           2

student_grade:

student_grade_id | student_id | grade_id
        1            20140540      1
        2            20140540      2

I tried to query like this but these gives me multiple row vertically with same values.

first | second
  80      80
  81      81

SELECT 1st.grade AS first, 2nd.grade as second FROM student_grade sg
INNER JOIN grade 1st ON 1st.grade_id = sg.grade_id
INNER JOIN grade 2nd ON 2nd.grade_id = sg.grade_id

WHERE sg.student_id = 20140540

GROUP BY 1st.grade, 2nd.grade;

Answer 1

What you're trying to do is a pivot, see MySQL pivot table for many solutions.

SELECT MAX(IF(g.grade_id = 1, g.grade, 0)) AS first,
       MAX(IF(g.grade_id = 2, g.grade, 0)) AS second
FROM grades AS g
JOIN student_grade as sg ON sg.grade_id = g.grade_id
WHERE sg.student_id = 20140540

There's no need for ORDER BY since this only returns 1 row.

DEMO