inner join in SQL server

Question

0.00/5 (No votes)

See more:

I have to following tables:

Student
	student_id
	fname
	lname
	year_id

Exam
	Exam_id
	Exam_name
	subject_id
	semester_id
        full mark
        pass mark

Semester
	semester_id
	semester_name
	year_id

Year
	year_id
	year_name

Subject
	subject_id
	subject_name
	year_id
	semester_id

Result
	Result_id
	student_id
	Exam_id
	score

Answers
      Answer ID
      Question ID
      correct_Answer
      The_Answer

Questions
       Question_ID
       Question Header
       Question_Group_Code
       Question_score
       Exam ID
       Subject ID


Question_Group
       Group_code
       Group_Description
       

I want to retrieve data reflect the most difficult questions in each exam? and The descending Sort students by the score of each student in each exam?

Posted 3-Oct-11 7:46am

Haanaza

Updated 6-Oct-11 21:48pm

v3

Add a Solution

Comments

OriginalGriff 3-Oct-11 14:11pm

What information are you trying to retrieve? Try giving a sample row that you want, making it clear what comes from where. That way we can help more accurately. At the moment, we would have to make assumptions, which may be wide of the mark.
Use the "Improve question" widget to edit your question and provide better information.

Haanaza 3-Oct-11 14:33pm

i have db include tables like ( Student - Subject - Exam - semester - year - Result) each table contain column,
i need to SQL statement that retrieve data about each student that Passed in subjects

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

André Kraak · Accepted Answer · 2011-10-03T13:11:00

My SQL knowledge is not extensive, but I think the SQl statement should be:

SQL

SELECT Student.fname, Student.lname, Subject.subject_name, Semester.semester_name, Year.year_name, MAX(Result.score) AS TopScore
FROM Result JOIN Student ON Result.student_id = Student.student_id
            JOIN Exam ON Result.Exam_id = Exam.Exam_id
            JOIN Subject ON Exam.subject_id = Subject.subject_id
            JOIN Semester ON Exam.semester_id = Semester.semester_id
            JOIN Year ON Semester.year_id = Year.year_id
GROUP BY Student.fname, Student.lname, Subject.subject_name, Semester.semester_name, Year.year_name