mysql - MySQL不存在联接表

我想检索这个：

        SELECT t.trainingID,trainingEvaluation,
    trainingAim, trainingHood, trainingExplanation, behaviorID,
        trainingTotalQuestion,
        trainingOK, trainingCreateTime
        FROM training t, trainingset ts, student s
        WHERE s.studentid = ts.studentid and
studentusername = 't' and t.trainingid = ts.trainingid

但如果不存在：

    SELECT distinct trainingResponseScore
        FROM training t, trainingset ts, student s,trainingresponse tr
        WHERE ts.studentid = tr.studentid and studentusername = 't'
and s.studentid = ts.studentid
     and tr.trainingid = ts.trainingid

我尝试不存在但会给出错误的地方。

而且这也不起作用

SELECT t.trainingID,trainingEvaluation, trainingAim, trainingHood, trainingExplanation, behaviorID, trainingTotalQuestion,
trainingOK, trainingCreateTime
FROM training t, trainingset ts, student s WHERE s.studentid = ts.studentid and studentusername = 't' and t.trainingid = ts.trainingid and t.trainingid not in (   SELECT distinct trainingResponseScore
        FROM training t, trainingset ts, student s,trainingresponse tr
        WHERE ts.studentid = tr.studentid and studentusername = 't'
and s.studentid = ts.studentid
     and tr.trainingid = ts.trainingid)

最佳答案

您正在使用20世纪后期的联接表方法：

   FROM table1, table2 WHERE table1.col = table2.col

在MySQL中，不可能以这种方式表示LEFT JOIN，因此，如果您的联接条件失败，则从结果集中删除行。

相反，您应该使用此模式

    FROM table1
    LEFT JOIN table2 ON table1.col = table2.col

对于您的查询，它可能看起来像这样：

SELECT t.trainingID,trainingEvaluation,
       trainingAim, trainingHood, trainingExplanation, behaviorID,
       trainingTotalQuestion,
       trainingOK, trainingCreateTime
  FROM student s
  LEFT JOIN trainingset ts    ON  s.studentid = ts.studentid
  LEFT JOIN student s         ON t.trainingid = ts.trainingid
  WHERE studentusername = 't'

这将为student中的每个匹配行产生至少一个结果集行。