many to many query with 1 row per result?

I have three tables
Student
-ID
-FirstName
<etc.>
Test
-ID
-Date
<etc.>
StudentTests
- TestID
- ChildID
- Grade
What I want to do is have a query that returns a student's information,
with a list of tests they've had. So, one row per student would be
ideal. I thought about changing table layout to have a fixed number of
tests, but I want to be able to change the number of tests pretty much
dynamically. Then I thought I could change table layout dynamically
(adding / removing columns as needed, and using dynamic SQL) but then I
thought that ... might not be the best idea :)
Right now I have this query
SELECT S.ID, S.FirstName, S.LastName, T.ID, T.Date, ST.Grade
FROM dbo.Student S
LEFT JOIN dbo.StudentTests ST ON ST.StudentID = S.ID
LEFT JOIN dbo.Test T ON T.ID = ST.TestID
ORDER BY S.LastName
Let's say there are 5 tests, I get 5 rows, I'm not super excited by
that, but I'm also not coming up with a way to change it.
So is there a way to do this?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. 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.
You might also want to learn ISO-11179 data element naming rules so
that when you do write DDL, it will be usable. Only one student,
magical "id" in the tables, reserved words, compound table names (do
you really say "mid-term exams" or "mid-term studenttest"?), etc. This
is sloppy even for a personal pseudo-code. Is this what you meant?
CREATE TABLE Students
(student_id INTEGER NOT NULL PRIMARY KEY,
.);
CREATE TABLE TestForms
(test_id INTEGER NOT NULL PRIMARY KEY,
test_date DATETIME NOT NULL,
.);
CREATE TABLE Exams
(student_id INTEGER NOT NULL
REFERENCES Students(student_id)
ON UPDATE CASCADE,
test_id INTEGER NOT NULL
REFERENCES TestForms(test_id)
ON UPDATE CASCADE,
test_grade CHAR(1) DEFAULT 'I' NOT NULL
CHECK ( test_grade IN ('I', 'W', 'A', 'B', 'C', 'D', 'F'),
PRIMARY KEY (student_id, test_id));
Did you know that you are supposed to do reports in application and not
the database in a tiered architecture?
Did you know that a table has a fixed number of columns by definition?
You are describing a report, which should be done in the front end.
Finally, you got something right! Dynamic SQL is a way of saying that
you have no idea what to do, so you will let someone else decide at run
time.
If the tests are attributes of an exam schedule, then each one gets a
column. just like height, weight and eye color would in a table that
models a person. If the tests are separate entities related to a
student, then each one gets a row in a gradebook or exams table.
This report is called a cross tabs and has been for the last 250+
years. So of course Microsoft calls it a PIVOT to be different. Her
is a quick way to write it in portable, standard SQL:
SELECT S1.student_id,
MAX(CASE WHEN T1.test_id = 1 THEN T1.test_grade ELSE '' END)
AS exam_1,
MAX(CASE WHEN T1.test_id = 2 THEN T1.test_grade ELSE '' END)
AS exam_2,
MAX(CASE WHEN T1.test_id = 3 THEN T1.test_grade ELSE '' END)
AS exam_3,
MAX(CASE WHEN T1.test_id = 4 THEN T1.test_grade ELSE '' END)
AS exam_4,
MAX(CASE WHEN T1.test_id = 5 THEN T1.test_grade ELSE '' END)
AS exam_5
FROM Students AS S1, Exams AS T1
WHERE S1.student_id = T1.student_id
GROUP BY S1.student_id;|||There are users of this group who have varying degrees of expertise.
If these questions bother you so much - STOP RESPONDING!!!! Aren't you
afraid that by being so abusive you are going to hurt your book sales?
--CELKO-- wrote:
> 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. 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.
> You might also want to learn ISO-11179 data element naming rules so
> that when you do write DDL, it will be usable. Only one student,
> magical "id" in the tables, reserved words, compound table names (do
> you really say "mid-term exams" or "mid-term studenttest"?), etc. This
> is sloppy even for a personal pseudo-code. Is this what you meant?
> CREATE TABLE Students
> (student_id INTEGER NOT NULL PRIMARY KEY,
> ..);
> CREATE TABLE TestForms
> (test_id INTEGER NOT NULL PRIMARY KEY,
> test_date DATETIME NOT NULL,
> ..);
> CREATE TABLE Exams
> (student_id INTEGER NOT NULL
> REFERENCES Students(student_id)
> ON UPDATE CASCADE,
> test_id INTEGER NOT NULL
> REFERENCES TestForms(test_id)
> ON UPDATE CASCADE,
> test_grade CHAR(1) DEFAULT 'I' NOT NULL
> CHECK ( test_grade IN ('I', 'W', 'A', 'B', 'C', 'D', 'F'),
> PRIMARY KEY (student_id, test_id));
>
> Did you know that you are supposed to do reports in application and not
> the database in a tiered architecture?
>
> Did you know that a table has a fixed number of columns by definition?
> You are describing a report, which should be done in the front end.
>
> Finally, you got something right! Dynamic SQL is a way of saying that
> you have no idea what to do, so you will let someone else decide at run
> time.
>
> If the tests are attributes of an exam schedule, then each one gets a
> column. just like height, weight and eye color would in a table that
> models a person. If the tests are separate entities related to a
> student, then each one gets a row in a gradebook or exams table.
> This report is called a cross tabs and has been for the last 250+
> years. So of course Microsoft calls it a PIVOT to be different. Her
> is a quick way to write it in portable, standard SQL:
> SELECT S1.student_id,
> MAX(CASE WHEN T1.test_id = 1 THEN T1.test_grade ELSE '' END)
> AS exam_1,
> MAX(CASE WHEN T1.test_id = 2 THEN T1.test_grade ELSE '' END)
> AS exam_2,
> MAX(CASE WHEN T1.test_id = 3 THEN T1.test_grade ELSE '' END)
> AS exam_3,
> MAX(CASE WHEN T1.test_id = 4 THEN T1.test_grade ELSE '' END)
> AS exam_4,
> MAX(CASE WHEN T1.test_id = 5 THEN T1.test_grade ELSE '' END)
> AS exam_5
> FROM Students AS S1, Exams AS T1
> WHERE S1.student_id = T1.student_id
> GROUP BY S1.student_id;|||Please send the table DDL, and a mock up of what your desired results looks
like.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<jwsolt@.gmail.com> wrote in message
news:1150901561.790662.157270@.b68g2000cwa.googlegroups.com...
>I have three tables
> Student
> -ID
> -FirstName
> <etc.>
> Test
> -ID
> -Date
> <etc.>
> StudentTests
> - TestID
> - ChildID
> - Grade
> What I want to do is have a query that returns a student's information,
> with a list of tests they've had. So, one row per student would be
> ideal. I thought about changing table layout to have a fixed number of
> tests, but I want to be able to change the number of tests pretty much
> dynamically. Then I thought I could change table layout dynamically
> (adding / removing columns as needed, and using dynamic SQL) but then I
> thought that ... might not be the best idea :)
> Right now I have this query
> SELECT S.ID, S.FirstName, S.LastName, T.ID, T.Date, ST.Grade
> FROM dbo.Student S
> LEFT JOIN dbo.StudentTests ST ON ST.StudentID = S.ID
> LEFT JOIN dbo.Test T ON T.ID = ST.TestID
> ORDER BY S.LastName
> Let's say there are 5 tests, I get 5 rows, I'm not super excited by
> that, but I'm also not coming up with a way to change it.
> So is there a way to do this?
>|||"Gary Gibbs" <ggibbs@.aahs.org> wrote in message
news:1150903867.254646.88710@.u72g2000cwu.googlegroups.com...
> There are users of this group who have varying degrees of expertise.
> If these questions bother you so much - STOP RESPONDING!!!! Aren't you
> afraid that by being so abusive you are going to hurt your book sales?
Here, here...|||jwsolt@.gmail.com wrote:
> What I want to do is have a query that returns a student's information,
> with a list of tests they've had. So, one row per student would be
> snipped
> Let's say there are 5 tests, I get 5 rows, I'm not super excited by
> that, but I'm also not coming up with a way to change it.
> So is there a way to do this?
>
I don't completely understand what you're looking for. You say you want
to return the student's information with a list of tests they've taken.
You then say that if there are five tests, you get back five records,
which is not what you want. If a student has taken five tests, and you
want a list of the tests that student has taken, why would you not want
five records returned?|||It seems that a cross-tab report presentation is what is desired. It should
happen at the client -or investigate SQL Reporting Services.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:OA3IfGVlGHA.4772@.TK2MSFTNGP03.phx.gbl...
> jwsolt@.gmail.com wrote:
> I don't completely understand what you're looking for. You say you want
> to return the student's information with a list of tests they've taken.
> You then say that if there are five tests, you get back five records,
> which is not what you want. If a student has taken five tests, and you
> want a list of the tests that student has taken, why would you not want
> five records returned?|||> I don't completely understand what you're looking for. You say you want
> to return the student's information with a list of tests they've taken.
> You then say that if there are five tests, you get back five records,
> which is not what you want. If a student has taken five tests, and you
> want a list of the tests that student has taken, why would you not want
> five records returned?
They want a pivot table of sorts.
e.g. Student Test 1 Test 2 Test 3 Test 4 Test 5
a 90 72 NULL 54 99
b NULL NULL NULL NULL 71
The problem is that SQL does not lend itself to figuring out how far across
you have to go. This kind of data shaping is definitely better for
client-side reporting tools.
http://www.aspfaq.com/2462|||Aaron Bertrand [SQL Server MVP] wrote:
> They want a pivot table of sorts.
> e.g. Student Test 1 Test 2 Test 3 Test 4 Test 5
> a 90 72 NULL 54 99
> b NULL NULL NULL NULL 71
> The problem is that SQL does not lend itself to figuring out how far acros
s
> you have to go. This kind of data shaping is definitely better for
> client-side reporting tools.
> http://www.aspfaq.com/2462
>
Ahhh, I get it now. His use of the phrase "list of tests" threw me...|||Personally I think his sales have been in decline for sometime and he tries
to drum up sales by being so abusive and ignorant - loudest voice gets
noticed and all that; if only he realised what a fool he has made of
himself.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Gary Gibbs" <ggibbs@.aahs.org> wrote in message
news:1150903867.254646.88710@.u72g2000cwu.googlegroups.com...
> There are users of this group who have varying degrees of expertise.
> If these questions bother you so much - STOP RESPONDING!!!! Aren't you
> afraid that by being so abusive you are going to hurt your book sales?
> --CELKO-- wrote:
>sql