subquery

Question

subquery

hlsc1983

SSCommitted

Points: 1971
More actions
April 2, 2017 at 4:14 am

#320065

this is my query
select Students.student_rollno, marks, Subjects.subject_name ,Subjects.subject_id, Gradetype.Grade_id, TheoryPractical .ThPr_name, EndtermProgressive. ETPA_name from Students_Subjects_junction join Subjects on Students_Subjects_junction.subject_id = Subjects.subject_id join Students on Students_Subjects_junction.student_pk =students.student_pk join Gradetype on Students_Subjects_junction .grade_id = Gradetype.grade_id join TheoryPractical on TheoryPractical.ThPr_id= Gradetype.ThPr_id join EndtermProgressive on EndtermProgressive.ETPA_id= Gradetype.ETPA_id where Students.semester_id = 7 and Students.department_id =14 and Students_Subjects_junction.year =2017
Now i want to add two more columns to the result: Maximum_Marks and Pass_Marks. These values are from the table Subjects_Gradetype_juncion
i tried some subqueries but am unable to get the solution. Please help. thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99133 More actions · Answer 1

I might be missing something here, screenshots are not a good way to portray your data, but why not add an extra join, to your Subjects_Gradetype_junction table, and return the values in your result set?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

hlsc1983 SSCommitted Points: 1971 More actions · Answer 2

k i did not explain myself properly.
What I am actually trying to do is this. The query above will give me the subect_id and grade_id for each row in the result of the query. And using these two values i want to get the Maximum_marks and Pass_marks from the Subjects_Gradetype_junction.
And finally i want to add/append these two values to the result of the query. So the final result of the query must contain all these values:
student_rollno, marks, Subjects.subject_name ,Subjects.subject_id, Gradetype.Grade_id, TheoryPractical .ThPr_name, EndtermProgressive. ETPA_name, Maximum_marks, Pass_marks

(i thought snapshots will actually make myself clearer)

hlsc1983 SSCommitted Points: 1971 More actions · Answer 3

Thom A - Monday, April 3, 2017 3:54 AM
I might be missing something here, screenshots are not a good way to portray your data, but why not add an extra join, to your Subjects_Gradetype_junction table, and return the values in your result set?

I can get the Maximum_marks and Pass_marks like this:
select Subject_id, Maximum_marks,Pass_marks from Subjects_Gradetype_junction where exists( select Students.student_rollno, marks, Subjects.subject_name ,Subjects.subject_id, Gradetype.Grade_id, TheoryPractical .ThPr_name, EndtermProgressive. ETPA_name from Students_Subjects_junction join Subjects on Students_Subjects_junction.subject_id = Subjects.subject_id join Students on Students_Subjects_junction.student_pk =students.student_pk join Gradetype on Students_Subjects_junction .grade_id = Gradetype.grade_id join TheoryPractical on TheoryPractical.ThPr_id= Gradetype.ThPr_id join EndtermProgressive on EndtermProgressive.ETPA_id= Gradetype.ETPA_id where Students.semester_id = 7 and Students.department_id =14 and Students_Subjects_junction.year =2017)

but like i said in my second post, i need the other columns as well. Please refer my second post.

Thom A SSC Guru Points: 99133 More actions · Answer 4

I still don't see any difference to your request to your original request. Does this not achieve what you want?
SELECT S2.student_rollno, marks, --Not sure what table this comes from. S.subject_name, S.subject_id, GT.Grade_id, TP.ThPr_name, EP.ETPA_name, SGTJ.maximum_marks, SGTJ.pass_marks FROM Students_Subjects_junction SSJ JOIN Subjects S ON SSJ.subject_id = S.subject_id JOIN Students S2 ON SSJ.student_pk = S2.student_pk JOIN Gradetype GT ON SSJ.grade_id = GT.grade_id JOIN TheoryPractical TP ON TP.ThPr_id = GT.ThPr_id JOIN EndtermProgressive EP ON EP.ETPA_id = GT.ETPA_id JOIN Subjects_Gradetype_junction SGTJ ON S.subject_id = SGTJ.subject_id AND GT.Grade_id = SGTJ.Grade_id WHERE S2.semester_id = 7 AND S2.department_id = 14 AND SSJ.[year] =2017;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

hlsc1983 SSCommitted Points: 1971 More actions · Answer 5

Thom A - Monday, April 3, 2017 7:30 AM
I still don't see any different here between your original request. Does this not achieve what you want?
SELECT S2.student_rollno, marks, --Not sure what table this comes from. S.subject_name, S.subject_id, GT.Grade_id, TP.ThPr_name, EP.ETPA_name, SGTJ.maximum_marks, SGTJ.pass_marks FROM Students_Subjects_junction SSJ JOIN Subjects S ON SSJ.subject_id = S.subject_id JOIN Students S2 ON SSJ.student_pk = S2.student_pk JOIN Gradetype GT ON SSJ.grade_id = GT.grade_id JOIN TheoryPractical TP ON TP.ThPr_id = GT.ThPr_id JOIN EndtermProgressive EP ON EP.ETPA_id = GT.ETPA_id JOIN Subjects_Gradetype_junction SGTJ ON S.subject_id = SGTJ.subject_id AND GT.Grade_id = SGTJ.Grade_id WHERE S2.semester_id = 7 AND S2.department_id = 14 AND SSJ.[year] =2017;

thank you so much. I was so easy . i guess i just dont know how joins work in the first place. little disappointed in myself.

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 6

hlsc1983 - Sunday, April 2, 2017 4:14 AM
this is my query
select Students.student_rollno, marks, Subjects.subject_name ,Subjects.subject_id, Gradetype.Grade_id, TheoryPractical .ThPr_name, EndtermProgressive. ETPA_name from Students_Subjects_junction join Subjects on Students_Subjects_junction.subject_id = Subjects.subject_id join Students on Students_Subjects_junction.student_pk =students.student_pk join Gradetype on Students_Subjects_junction .grade_id = Gradetype.grade_id join TheoryPractical on TheoryPractical.ThPr_id= Gradetype.ThPr_id join EndtermProgressive on EndtermProgressive.ETPA_id= Gradetype.ETPA_id where Students.semester_id = 7 and Students.department_id =14 and Students_Subjects_junction.year =2017
Now i want to add two more columns to the result: Maximum_Marks and Pass_Marks. These values are from the table Subjects_Gradetype_juncion
i tried some subqueries but am unable to get the solution. Please help. thanks.

Please post DDL and follow ANSI/ISO standards when asking for help.