🔒 Locked

[web] Need help with a query

Started by leiavoia Aug 27, 2006 at 2:40 PM 4 replies 650+ views

Original Post

Aug 27, 2006 at 2:40 PM

I'm having trouble putting a query together and i was hoping you might be able to help. For this example, i'm creating a testing/grading scenario. We have students, courses, and tests_taken tables like so: STUDENTS - id - username COURSES - id - course_title TESTS_TAKEN - user_id - course_id - score What i want to do is spit out a table that prints all the available courses in the database (any student can take any course) and the score (if any) the student got on the final test for that course. Each course has only one test. You either pass or fail. If a student has not taken the final exam for the course, there will be no entry in the TESTS_TAKEN table. Courses are sequential. Ideally, i would like to have output like this: [ logged in as "username" ] 85% FOO 101 73% BAR 101 77% HotDogs For Dummies --- Quantum Physics for Dummies --- Advanced Car Washing --- Master Class Chess This shows all the courses available (all 6) and that the logged-in user has completed the final exam on the first 3 courses. I'm looking for the proper SQL query for this. I know i have to use an OUTER JOIN to get the basic data, but i also have to limit the test scores data to the user_id of the logged in user. That's what' tripping me up. Thanks for your help!

ToohrVyk

Aug 27, 2006 at 2:55 PM

I would probably do something like this:

SELECT course_title, AVG(score)FROM courses, tests_takenWHERE tests_taken.user_id = $user_id  AND courses.id = tests_taken.course_idGROUP BY course_title

Though, off the top of my head, I can't say for sure what happens if there are no scores for a given course.

hplus0603

Aug 27, 2006 at 3:09 PM

If there is no row in the tests-taken table, then it will not be part of the output.

What you want is an outer join (where NULL is included). As databases typically optimize left outer joins better than right outer joins, your query will look something like:

select course_title, scorefrom COURSES   left outer join TESTS_TAKEN  on course_id = idwhere user_id = :student_id;

Mathachew

Aug 27, 2006 at 3:36 PM

I don't know if the above queries will work since selected fields are not identified to their respective tables (I'm used to PostgreSQL, which is strict about field selection/comparison; MySQL may differ). He also has "left outer join", which I've never seen and don't think will work as intended.

SELECT    courses.course_title,    tests_taken.scoreFROM    coursesOUTER JOIN    tests_taken ON    tests_taken.course_id = courses.idWHERE    tests_taken.user_id = $user_id;

This is pretty much a combo of both of the responses. I just wanted to bring a little clarification. The outer join, as hplus said, will give you blank results for any tests that apply.

Nik02

Aug 27, 2006 at 4:32 PM

Left outer join more precisely expresses what the original poster wanted to do. A plain outer join will include non-matching data from both joined tables, while the left version only does from the right-hand side (so left-side table is the primary "driver" of the joint data).

Mathachew

Aug 27, 2006 at 8:50 PM

Quote:
Original post by Nik02
Left outer join more precisely expresses what the original poster wanted to do. A plain outer join will include non-matching data from both joined tables, while the left version only does from the right-hand side (so left-side table is the primary "driver" of the joint data).

Ah, learning something new everyday is good! :)

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.