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

    Join Date
    Sep 2012
    Posts
    3
    Rep Power
    0

    Unknown column 'InstructorID' in 'on clause'


    I have seen this error on this forum before and I have read the help documents and PHP information on changing this statement to 5.*, however, I can't seem to get it right.

    What I have is:

    $Query = runquery(
    'SELECT Dispatch.*, Student.FirstName AS SFirstName, Student.LastName AS SLastName, Instructor.FirstName AS IFirstName, Instructor.LastName As ILastName
    FROM Dispatch, Student
    LEFT JOIN Instructor ON InstructorID = Instructor.ID
    WHERE Student.ID = Dispatch.StudentID ');

    Unfortunately I am getting an error, despite how I try to re-write this code which states:

    Error was: Unknown column 'InstructorID' in 'on clause'

    Can anybody else me better understand why this is occuring? I know that I need to re-write the LEFT JOIN but I am not entirely sure how.

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

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by NightBeam
    Can anybody else me better understand why this is occuring? I know that I need to re-write the LEFT JOIN but I am not entirely sure how.
    nope, it's your inner join that needs re-writing

    as to why it's happening, search this forum for "unknown column" -- this has been answered a couple dozen times since mysql went from version 4 to version 5

    which table is InstructorID in?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    3
    Rep Power
    0
    Thank you for that. I managed to get it to work with a little trial-and-error on the INNER JOIN

    I ended up with:

    $Query = runquery(
    'SELECT Dispatch.*, Student.FirstName AS SFirstName, Student.LastName AS SLastName, Instructor.FirstName AS IFirstName, Instructor.LastName As ILastName
    FROM Dispatch
    INNER JOIN Student on Student.ID
    LEFT JOIN Instructor ON InstructorID = Instructor.ID
    WHERE Student.ID = Dispatch.StudentID

    Instructor.ID is in the Dispatch Table.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    3
    Rep Power
    0
    Sorry, I meant to say that that it was in the Dispatch Table
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by NightBeam
    FROM Dispatch
    INNER JOIN Student on Student.ID
    that's incomplete

    that will actually join every row of dispatch to every row of student, because Student.ID by itself is going to evaluate as TRUE

    your WHERE clause then throws almost all of the joined rows away

    what you need to do is move the WHERE condition to that INNER JOIN's ON clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo