MYSQL比较加入问题 [英] MYSQL compare on join problem

问题描述

接着从 MySQL多个ID查找

所以我正在研究围绕全文搜索对视图的限制的其他方式.

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'

最后我得到Unknown列名1，我什至不能对数据做简单查询吗?

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

推荐答案

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)

(仅在两个表均为MyISAM时有效)

(works only if both tables are MyISAM)

这将返回同时教授English和Physics的所有老师.

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

这篇关于MYSQL比较加入问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！