MYSQL compare on join problem

Question

Following on from http://stackoverflow.com/questions/3865384/mysql-multiple-id-lookups

So I'm looking at other ways around the fulltextsearch limitation on views.

I updated the provided query...

SELECT  t.teacherId, s1.subjectName AS name1, s2.subjectName AS name2, s3.subjectName AS name3
FROM    teacherProfile t
LEFT JOIN
        subjects s1
ON      s1.subjectId = t.subjectOne 
LEFT JOIN
        subjects s2
ON      s2.subjectId = t.subjectTwo
LEFT JOIN
        subjects s3
ON      s3.subjectId = t.subjectThree

Which works a treat at least, but if I add..

WHERE name1 = ENGLISH'

to the end I get Unknown column name1, can I not even do a simple query on the data?

Answer 1

SELECT  t.teacherId, s1.subjectName AS name1, s2.subjectName AS name2, s3.subjectName AS name3
FROM    teacherProfile t
LEFT JOIN
        subjects s1
ON      s1.subjectId = t.subjectOne 
LEFT JOIN
        subjects s2
ON      s2.subjectId = t.subjectTwo
LEFT JOIN
        subjects s3
ON      s3.subjectId = t.subjectThree
WHERE   'English' IN (s1.subjectName, s2.subjectName, s3.subjectName)

, or, referring to your original problem,

SELECT  t.teacherId, s1.subjectName AS name1, s2.subjectName AS name2, s3.subjectName AS name3
FROM    teacherProfile t
LEFT JOIN
        subjects s1
ON      s1.subjectId = t.subjectOne 
LEFT JOIN
        subjects s2
ON      s2.subjectId = t.subjectTwo
LEFT JOIN
        subjects s3
ON      s3.subjectId = t.subjectThree
WHERE   MATCH(s1.subjectName, s2.subjectName, s3.subjectName) AGAINST ('+English +Physics' IN BOOLEAN MODE)

(works only if both tables are MyISAM)

This will return all teachers who teach both English and Physics.

Answer 2

you can't reference column labels; you have to reference the column, like so:

WHERE s1.subjectName = 'ENGLISH'

Answer 3

you are getting unknown column name because you have syntax error

change it to

s1.subjectName = 'ENGLISH'