CODEWITHSUNDEEP
mysqlsql
Arcaz
Arcaz

Reputation: 53

Query for getting students and grades SQL

I have a students table Student(LRN,fName,lName,levelID) and a grade table GRADE(subjectID,grade,levelID,LRN,TimeAdded) I want to show all students with all of thier subjects with grades in on line. I tired this query but I got this results.

Query: SELECT student.LRN, student.fName, student.lName, 
(SELECT grade.grade WHERE grade.subjectID = 'k2arts') AS 'k2arts',
(SELECT grade.grade WHERE grade.subjectID = 'k2math') AS 'k2math',
(SELECT grade.grade WHERE grade.subjectID = 'k2eng') AS 'k2eng',
(SELECT grade.grade WHERE grade.subjectID = 'k2valed') AS 'k2valed',
(SELECT grade.grade WHERE grade.subjectID = 'k2fil') AS 'k2fil' 
from student RIGHT JOIN grade on grade.LRN=student.LRN

Results: Query Result

Upvotes: 0

Views: 2306

Answers (3)

JNevill
JNevill

Reputation: 50034

You want to establish the relationship of the STUDENT in each subquery. You do not need to join in Grade in the main query since you aren't using any of its values except by way of subqueries, which are in an unrelated context in this case:

SELECT student.LRN, student.fName, student.lName, 
(SELECT grade.grade FROM grade WHERE grade.subjectID = 'k2arts' AND grade.LRN=st.LRN) AS 'k2arts',
(SELECT grade.grade FROM grade WHERE grade.subjectID = 'k2math' AND grade.LRN=st.LRN) AS 'k2math',
(SELECT grade.grade FROM grade WHERE grade.subjectID = 'k2eng' AND grade.LRN=st.LRN) AS 'k2eng',
(SELECT grade.grade FROM grade WHERE grade.subjectID = 'k2valed' AND grade.LRN=st.LRN) AS 'k2valed',
(SELECT grade.grade FROM grade WHERE grade.subjectID = 'k2fil' AND grade.LRN=st.LRN) AS 'k2fil' 
from student st

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

You could use a left join and a case when 

SELECT student.LRN, student.fName, student.lName, 
  case when grade.subjectID = 'k2arts' then grade.grade else null end AS 'k2arts',
  case when grade.subjectID = 'k2math' then grade.grade else null end AS 'k2math',
  case when grade.subjectID = 'k2eng' then grade.grade else null end AS 'k2eng',
  case when grade.subjectID = 'k2valed' then grade.grade else null end AS 'k2valed',
  case when grade.subjectID = 'k2fil' then grade.grade else null end AS 'k2fil'
from student 
LEFT JOIN grade on grade.LRN=student.LRN

and eventually use a fake aggregation function for remove null 

SELECT student.LRN, student.fName, student.lName, 
  min(case when grade.subjectID = 'k2arts' then grade.grade else null end) AS 'k2arts',
  min(case when grade.subjectID = 'k2math' then grade.grade else null end) AS 'k2math',
  min(case when grade.subjectID = 'k2eng' then grade.grade else null end) AS 'k2eng',
  min(case when grade.subjectID = 'k2valed' then grade.grade else null end) AS 'k2valed',
  min(case when grade.subjectID = 'k2fil' then grade.grade else null end) AS 'k2fil'
from student  student.LRN, student.fName, student.lName
LEFT JOIN grade on grade.LRN=student.LRN 
group by

Upvotes: 0

Vash
Vash

Reputation: 1787

Try: 

SELECT student.LRN, student.fName, student.lName, 
CASE WHEN grade.subjectID = 'k2arts' THEN grade.grade END AS 'k2arts',
CASE WHEN grade.subjectID = 'k2math'  THEN grade.grade END AS 'k2math',
CASE WHEN grade.subjectID = 'k2eng'   THEN grade.grade END AS 'k2eng',
CASE WHEN grade.subjectID = 'k2valed' THEN grade.grade END AS 'k2valed',
CASE WHEN grade.subjectID = 'k2fil'   THEN grade.grade END AS 'k2fil' 
from student 
LEFT JOIN grade 
on grade.LRN=student.LRN ;

Upvotes: 0

Related Questions