Help with query

[Joao Vieira]

Hello,

I have a problem with a query i'm trying to create.

I have a site that provides courses and at the end of each lesson the
students take a test with multiple choice answers. All that
information is stored in a results table that has the following
structure:

Course_id (the id of the course that relates to a Courses table)
Module_id (the id of the module – or lesson – that relates to a
Modules table)
User_id (the id of the user that relates to a User table)
Total (the total score of the test)

The problem is that I'm trying to create a report that gives the
scores the students had in each test of each course but sometimes they
haven't taken all the tests and I wanted the report to reflect that.

For example, for now I can make the following report:

Course: VB 6.0
User: Paul Smith
Test 1: 20
Test 2: 30
Test 3: 50
Test 5: 40
Test 7: 30
User: Judy Clarkson
Test 1: 30
Test 2: 20

But I would like to show all the tests even the ones not taken yet,
for example:

Course VB 6.0
User Paul Smith
Test 1: 20
Test 2: 30
Test 3: 50
Test 4: ---
Test 5: 40
Test 6: ---
Test 7: ---
User: Judy Clarkson
Test 1: 30
Test 2: 20
Test 3: ---
Test 4: ---
Test 5: ---
Test 6: ---
Test 7: ---

Can someone help me please ?
Thanks,

Joao

[David Portas]

The best way to get help for a problem like this is to post the DDL (CREATE
TABLE statements), a few rows of sample data (as INSERT statements) and show
your required result.

Unfortunately you've missed out some relevant information. It's not clear to
me what all the keys are. It looks like you could have a many-to-many
relationship between Courses and Modules (otherwise why would you have both
columns in the Results table?) but you haven't explained how we know which
Course relates to which Module. For this example I will assume that there is
a CourseModules table that relates the two.

Here's my assumed DDL:

CREATE TABLE Users (user_id INTEGER PRIMARY KEY, user_name VARCHAR(30) NOT
NULL UNIQUE)

CREATE TABLE Courses (course_id INTEGER PRIMARY KEY)

CREATE TABLE Modules (module_id INTEGER PRIMARY KEY)

CREATE TABLE CourseModules (course_id INTEGER REFERENCES Courses
(course_id), module_id INTEGER REFERENCES Modules (module_id), PRIMARY KEY
(course_id,module_id))

CREATE TABLE CourseResults (course_id INTEGER NOT NULL REFERENCES Courses
(course_id), module_id INTEGER NOT NULL, user_id INTEGER NOT NULL,
total_score INTEGER NOT NULL, FOREIGN KEY (course_id, module_id) REFERENCES
CourseModules (course_id,module_id), PRIMARY KEY
(course_id,module_id,user_id))

Here's the query I think you need.

SELECT U.user_name, M.course_id, M.module_id, R.total_score
FROM CourseModules AS M
JOIN (SELECT DISTINCT user_id, course_id
FROM CourseResults) AS N
ON M.course_id = N.course_id
JOIN Users AS U
ON U.user_id = N.user_id
LEFT JOIN CourseResults AS R
ON M.course_id = R.course_id
AND M.module_id = R.module_id
AND U.user_id = R.user_id
ORDER BY U.user_name, M.course_id, M.module_id

Notice that this returns all modules for any course for which a user has
completed at least 1 module. If you want to include *all* courses for every
user:

SELECT U.user_name, M.course_id, M.module_id, R.total_score
FROM CourseModules AS M
CROSS JOIN Users AS U
LEFT JOIN CourseResults AS R
ON M.course_id = R.course_id
AND M.module_id = R.module_id
AND U.user_id = R.user_id
ORDER BY U.user_name, M.course_id, M.module_id

Hope this helps.

--
David Portas
SQL Server MVP
--

[David Portas]

The best way to get help for a problem like this is to post the DDL (CREATE
TABLE statements), a few rows of sample data (as INSERT statements) and show
your required result.

Unfortunately you've missed out some relevant information. It's not clear to
me what all the keys are. It looks like you could have a many-to-many
relationship between Courses and Modules (otherwise why would you have both
columns in the Results table?) but you haven't explained how we know which
Course relates to which Module. For this example I will assume that there is
a CourseModules table that relates the two.

Here's my assumed DDL:

CREATE TABLE Users (user_id INTEGER PRIMARY KEY, user_name VARCHAR(30) NOT
NULL UNIQUE)

CREATE TABLE Courses (course_id INTEGER PRIMARY KEY)

CREATE TABLE Modules (module_id INTEGER PRIMARY KEY)

CREATE TABLE CourseModules (course_id INTEGER REFERENCES Courses
(course_id), module_id INTEGER REFERENCES Modules (module_id), PRIMARY KEY
(course_id,module_id))

CREATE TABLE CourseResults (course_id INTEGER NOT NULL REFERENCES Courses
(course_id), module_id INTEGER NOT NULL, user_id INTEGER NOT NULL,
total_score INTEGER NOT NULL, FOREIGN KEY (course_id, module_id) REFERENCES
CourseModules (course_id,module_id), PRIMARY KEY
(course_id,module_id,user_id))

Here's the query I think you need.

SELECT U.user_name, M.course_id, M.module_id, R.total_score
FROM CourseModules AS M
JOIN (SELECT DISTINCT user_id, course_id
FROM CourseResults) AS N
ON M.course_id = N.course_id
JOIN Users AS U
ON U.user_id = N.user_id
LEFT JOIN CourseResults AS R
ON M.course_id = R.course_id
AND M.module_id = R.module_id
AND U.user_id = R.user_id
ORDER BY U.user_name, M.course_id, M.module_id

Notice that this returns all modules for any course for which a user has
completed at least 1 module. If you want to include *all* courses for every
user:

SELECT U.user_name, M.course_id, M.module_id, R.total_score
FROM CourseModules AS M
CROSS JOIN Users AS U
LEFT JOIN CourseResults AS R
ON M.course_id = R.course_id
AND M.module_id = R.module_id
AND U.user_id = R.user_id
ORDER BY U.user_name, M.course_id, M.module_id

Hope this helps.

--
David Portas
SQL Server MVP
--

[Joao Vieira]

Hi David,

Sorry about the CourseModule table. I mistakenly did not referenced it.

And thanks for your reply. It's exactly what I was looking for.