getting data from 3 tables and giving MAX numbr

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

getting data from 3 tables and giving MAX numbr

Author

Topic

asad_bhalu
Starting Member

2 Posts

Posted - 2011-01-18 : 07:25:37

hello everyone,
I've been trying to sort this problem of mine for last 2 days

there are 3 tables
'student_info'
'courselist'
'student_course'
using join, I connected these tables

to get max marks of a student in a particular course

SELECT
student_info.fname AS S_Name,
courselist.course_name AS Course,
student_course.marks AS Max_M
FROM student_course
INNER JOIN student_info
ON student_info.s_id = student_course.s_id
INNER JOIN courselist
ON courselist.c_id = student_course.c_id
WHERE (courselist.course_name = 'c++'
AND marks = (SELECT
MAX(student_course.marks)
FROM student_course))

//my problem is that it gives me the max marks
and also the course name but the name column gives me the
1st name it takes from the table
//2nd thing is that MAX function takes max marks from
the whole column. not from the particular course i've selected

kindly, guide me in the right direction,
Thankyou.

A.Q

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-18 : 11:59:53

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

Don't we call "Student_Courses" something like "Class_Roster"' Do you say "Husband_Wife" or "Marriages"? No, the relationship has a name. Do you say couirse list or call it by the dcolelctive name "Course_Catalog"?

A good design has proper data element names and we do not re-name them in the SELECT clause list. Now, he is me doing your job for you; how good were my guesses in this skeleton?

CREATE TABLE Students
(student_id ..PRIMARY KEY,
..);

CREATE TABLE Course_Catalog
(course_nbr ..PRIMARY KEY,
course_name ..
..);

Since you did bother with minimal DDL, I don't know the key and that it vital. My guess from y9ur narrative is that a course can be taken over and over

CREATE TABLE Class_Roster
(student_id .. REFERENCES Students(student_id),
course_nbr .. REFERENCES Course_catalog(course_nbr),
attempt_cnt INTEGER DEFASULT 1 NOT NULL,
PRIMARY KEY (student_id, course_nbr, attempt_cnt),
course_mark ..
..)

>> to get max course_mark of a student in a particular course <<

SELECT S.student_first_name, C.course_name,
MAX(CR.course_mark) AS course_mark_max
FROM Class_Roster AS CR,
Students AS S,
Course_Catalog AS C
WHERE S.student_id = CR.student_id
AND C.course_nbr = CR.course_nbr
AND C.course_name = 'C++'
GROUP BY S.student_first_name, C.course_name;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

asad_bhalu
Starting Member

2 Posts

Posted - 2011-01-19 : 08:03:18

//this is the courselist table
//c_id with the primary key

c_id, course_name

//student_info table
//s_id with the primary key

s_id, fname, lname, address, city

//class_roster table (its an intermediate table)
//sc_id with primary key
// c_id & s_id are the foreign keys

sc_id, s_id, c_id, marks,

SELECT
  student_info.fname     ,
  courselist.course_name ,
  class_roster.marks   
FROM testdb.class_roster
  INNER JOIN testdb.student_info
    ON student_info.s_id = student_course.s_id
  INNER JOIN testdb.courselist
    ON courselist.c_id = student_course.c_id
WHERE (courselist.course_name = 'c++'
       AND marks = (SELECT
                      MAX(class_roster.marks)
                    FROM class_roster))

->it gives me the the max marks! n also the right course name
->but the issue is that it gives me the 1st name it selects from the fname column from student_info. I've given the query to match the "s_id" but still am not getting the desired output!
-> which is to get the Max marks in a particular course of a student!

>> i hope now people will understand now
>> n thanks "jcelko" for that article. it'll help me in the future

A.Q

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources