#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    12
    Rep Power
    0

    Question regarding SELECT


    Hi,

    I have these two tables:

    students:

    id | last_name
    ---------------------
    1 | Thomas
    2 | Andrews
    3 | Scott
    4 |


    grades:
    (the student_id here is linked to the id from above).

    student_id | grade
    ----------------------------
    2 | E
    1 | A
    3 | B
    4 | E
    6 | F

    Now, there isn't a student with id '6' from the student table, but is in the student_id.

    I send over the following SQL statement:

    "SELECT id, grade from grades where student_id=students.id ORDER BY students.last_name"

    This is fine. It finds the students in both the grade table AND the students table and displays them in order by their last name.

    However, what about for id '6', where they are not in the student table? If I do:

    "SELECT id, grade from grades where student_id!=studentds_id"

    This outputs ALL the student id's in the table.

    Are there any other methods I could do this by? I just want a list of students not in the students table....

    Thank you

    Ogden
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    "a list of students not in the students table..."

    select G.student_id, G.grade
    from grades G
    left outer
    join students S
    on G.student_id = S.id
    where S.id is null

    rudy
    http://r937.com/
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    12
    Rep Power
    0
    Thank you very much. I will have to read up on it to understand it more as I don't even know what a "Left outer join" is...

    Thank you!

    Ogden

IMN logo majestic logo threadwatch logo seochat tools logo