#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    2
    Rep Power
    0

    Column comparison to other table


    I know this should be pretty simple but my inexperience is showing here

    I have a table called Grades and a table called Course.

    Grades show a student id, course id, grade and grader (teacher).

    Course shows course id and teacher.

    I need to query for all rows in Grades that have a different grader from the course teacher.

    So if I have student s01, course a100, grade 3, grader t120

    and the course a100 has teacher t130

    the row should show up. I messed around with join commands and I got close, but then I somehow messed up and got a mixed result.

    This is what I tried:

    SELECT studentno, grade, grader
    FROM Grades JOIN Course ON(Grades.grader = Course.teacherno)
    WHERE Grades.grader = Course.teacherno

    That to my mind would return list of all rows that have the same teacher as grader as is teaching the course. Then I tried to reverse it by != command:

    SELECT studentno, grade, grader
    FROM Grades JOIN Course ON(Grades.grader = Course.teacherno)
    WHERE Grades.grader != Course.teacherno

    But it returns no rows. Obviously I have a logic fault here. Can you guys help me?
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    The first step is to get the course for every grade. So JOIN the grades with the courses ON the course_id:
    Code:
    Grades
    JOIN Course ON (Grades.course_id = Course.id)
    (I don't know your exact database design, so I had to guess)

    And now you select those grades where the grader is different from the course teacher:
    Code:
    SELECT
    	Grades.studentno,
    	Grades.grade,
    	Grades.grader
    FROM 
    	Grades
    	JOIN Course ON (Grades.course_id = Course.id)
    WHERE
    	Grades.grader <> Course.teacherno
    ;
    By the way, your naming is a bit strange. Capitalized names are a bad idea, because they're rather error prone, annoying to type and will lead to difficulties on other database systems.

    Comments on this post

    • debasisdas agrees
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    2
    Rep Power
    0
    That works perfectly! I KNEW I got close, but I tried to use the wrong join command because I didn't understand the logic what I should join and why.

    Thanks very much. I hope you don't think of me as unappreciative because it took so long for me to reply with my thanks!

IMN logo majestic logo threadwatch logo seochat tools logo