#1054 - Unknown column in 'on clause'

Question

I am getting this error:

#1054 - Unknown column 'examinationresults.userid' in 'on clause'

I've seen other posts on this but I can't seem to figure it out. I don't think I'm referring to anything out of order...

SELECT examinationresults.id AS id, examinationresults.examid AS examid, 
examination.tag AS examtag, examination.title AS examtitle, examinationresults.revision AS revision, 
examination.medicalspecialtyid AS medicalspecialtyid, medicalspecialties.title AS medicalspecialtytitle, 
examination.institutionid AS institutionid, institutions.title AS institutiontitle, 
examinationresults.grade AS grade, examinationresults.points AS points, examinationresults.pointsof AS pointsof, 
examinationresults.datebegan AS datebegan, examinationresults.datefinished AS datefinished, 
examinationresults.totaltime AS totaltime, examinationresults.finished AS finished, examinationresults.graded AS graded, 
users.username AS username, users.firstname AS firstname, users.middlename AS middlename, users.lastname AS lastname 
FROM examinationresults, examination 
INNER JOIN medicalspecialties 
    ON examination.medicalspecialtyid=medicalspecialties.id 
INNER JOIN institutions 
    ON examination.institutionid=institutions.id 
INNER JOIN users 
    ON examinationresults.userid=users.id 
WHERE examination.examid=examinationresults.examid AND examination.revision=examinationresults.revision AND examinationresults.userid='1' 
ORDER BY examinationresults.datefinished ASC;

score 7 · Accepted Answer · answered Nov 20 '13 at 20:47

7

You have

FROM examinationresults, examination 
INNER JOIN medicalspecialties /*....*/

Don't mix ANSI 89 and ANSI 92 join styles.

Use the INNER JOIN syntax for examinationresults and examination too.

FROM   examinationresults
       INNER JOIN examination
         ON examination.examid = examinationresults.examid
            AND examination.revision = examinationresults.revision
       INNER JOIN medicalspecialties
         ON examination.medicalspecialtyid = medicalspecialties.id
       INNER JOIN institutions
         ON examination.institutionid = institutions.id
       INNER JOIN users
         ON examinationresults.userid = users.id
WHERE  examinationresults.userid = '1'

answered Nov 20 '13 at 20:47

Martin Smith

438,706
87
741
845

Perfect. That makes so much more sense... It really was doing the same thing in two different ways by using FROM/WHERE to join... INNER JOIN (ANSI 92 I assume?) is much better and clearer. – Edward Nov 20 '13 at 21:03
1

@Edward - Yes inner join is the ANSI 92 version. If you mix the join types you also end up with a confusing mix of precedence. I just noticed when googling the precedence issue that this is a dupe actually. [More explanation in the answer here](http://stackoverflow.com/a/4065993/73226) – Martin Smith Nov 20 '13 at 21:07

score 2 · Answer 2 · edited Jul 29 '20 at 18:25

You forget to use the INNER JOIN syntax for examinationresults and examination .Try this:

SELECT examinationresults.id AS id, examinationresults.examid AS examid, 
examination.tag AS examtag, examination.title AS examtitle, examinationresults.revision AS revision, 
examination.medicalspecialtyid AS medicalspecialtyid, medicalspecialties.title AS medicalspecialtytitle, 
examination.institutionid AS institutionid, institutions.title AS institutiontitle, 
examinationresults.grade AS grade, examinationresults.points AS points, examinationresults.pointsof AS pointsof, 
examinationresults.datebegan AS datebegan, examinationresults.datefinished AS datefinished, 
examinationresults.totaltime AS totaltime, examinationresults.finished AS finished, examinationresults.graded AS graded, 
users.username AS username, users.firstname AS firstname, users.middlename AS middlename, users.lastname AS lastname 

FROM   examinationresults
       INNER JOIN examination
         ON examination.examid = examinationresults.examid
            AND examination.revision = examinationresults.revision
       INNER JOIN medicalspecialties
         ON examination.medicalspecialtyid = medicalspecialties.id
       INNER JOIN institutions
         ON examination.institutionid = institutions.id
       INNER JOIN users
         ON examinationresults.userid = users.id
WHERE  examinationresults.userid = '1'

On a side note:

You may check

Bad habits to kick : using old-style JOINs

by Aaron Bertrand for reference.

#1054 - Unknown column in 'on clause'

2 Answers2

Bad habits to kick : using old-style JOINs