sql query
-
From the tables given below i need to know the values of 'ThPr_name' from TheoryPractical table and 'ETPA_name' from EndtermProgressive table
This is my query so far but i cant get the above values.select Students.student_rollno, marks, Subjects.subject_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
where Students.semester_id = 7 and Students.department_id =14 and Students_Subjects_junction.year =2017And if there are glaring errors or improvements needed in my design, do let me know.
- hlsc1983 - Thursday, March 30, 2017 12:31 PMFrom the tables given below i need to know the values of 'ThPr_name' from TheoryPractical table and 'ETPA_name' from EndtermProgressive table
This is my query so far but i cant get the above values.select Students.student_rollno, marks, Subjects.subject_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
where Students.semester_id = 7 and Students.department_id =14 and Students_Subjects_junction.year =2017And if there are glaring errors or improvements needed in my design, do let me know.
You would need to include the tables that have those columns. To do so, you need an intermediate table as well. Just add more joins and post what you tried if you still can't get results.
-
select Students.student_rollno, marks, Subjects.subject_name ,Gradetype.Grade_id
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
where Students.semester_id = 7 and Students.department_id =14 and Students_Subjects_junction.year =2017Ik i can get the Grade_id this way but what now?
- hlsc1983 - Thursday, March 30, 2017 1:12 PM
Now you need to join the other to tables using the columns from GradeType. By the way, this might help your readability.
SELECT
stu.student_rollno,
ssj.marks,
sub.subject_name,
gt.Grade_id
FROM Students_Subjects_junction ssj
JOIN Subjects sub on ssj.subject_id = sub.subject_id
JOIN Students stu on ssj.student_pk = stu.student_pk
JOIN GradeType gt ON ssj.grade_id = gt.Grade_id
WHERE stu.semester_id = 7
AND stu.department_id = 14
AND ssj.year = 2017 -
k i got it!!! i just didnt know joins worked like this.
select Students.student_rollno, marks, Subjects.subject_name ,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.Grade_id
join EndtermProgressive on EndtermProgressive.ETPA_id= Gradetype.Grade_id
where Students.semester_id = 7 and Students.department_id =14 and Students_Subjects_junction.year =2017
-
Home .But i am not getting the desired values. These are the values in the tables.
And when i run the query i get this result.
Now for grade_id 2 i was expecting the values 'Theory' and 'Progressive Assessment' but i am getting 'Practical' and 'Progressive Assessment'
Obviously my quesry is incorrect.
- hlsc1983 - Thursday, March 30, 2017 1:35 PMHome .But i am not getting the desired values. These are the values in the tables.
And when i run the query i get this result.
Now for grade_id 2 i was expecting the values 'Theory' and 'Progressive Assessment' but i am getting 'Practical' and 'Progressive Assessment'
Obviously my quesry is incorrect.
You're not using the correct columns in your joins. You need to use the foreign key columns in Gradetype to join with the primary key columns in the other 2 tables. In these case you can identify them because they have exactly the same column name.
-
thanks. got it!!
is my table design alright? - hlsc1983 - Thursday, March 30, 2017 1:46 PM
Hard to say. As you might have noticed, designing a database is not an easy task. You got plenty of help on this previous thread and you're probably past the design stage.
As said before, the design of a database/table depends on the needs of the business. - hlsc1983 - Thursday, March 30, 2017 1:46 PM
No. Here are a few thoughts on that, although this list is certainly not everything that needs reviewed:
Table names should be consistent: either all singular or all plural. Similarly, if you insist on a column prefix such as "subject", then use it consistently, always with underscore ("_") after or never, but don't mix the two, as in "subject_name" and "subjectTHET". Pick one naming style and stick to it. Also, no column name should include "_pk" (or "_fk" for that matter). That's not part of the data name, that's just a (current) data key relationship.
Semester is an Entity of its own, and has its own attributes, and thus needs its own table ("Semesters" or some such).
Semester{_id/_code} is not typically stored within Subjects / Courses. For some schools, a "course" is equivalent to your "subject", for others a "Course" is the combination of a Subject and a given Semester. For example, "Introduction to Physics", aka "Physics 101", is a subject, but it could have multiple Course offerings in a given Semester, such as "Fall 2017". Each course is separate, and will of course have different student enrollments, etc.. In rare cases, could even have different start (and end) dates. A student's mark is for a specific course. A given student can retake a subject, and thus have multiple marks for the subject, but not for the same Course (unless that is somehow valid within that particular school's grading system).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply