mysql - 为什么此 WHERE 子句不适用于 Join？

Question

我对 SQL 很陌生，这是一个相当复杂的命令:

SELECT users.name, 
       from_unixtime(activity.time_stamp, "%D %b %Y  %l:%i:%S"), 
      activity.activity, 
      activity.rfid_tag, 
      chemicals.description 
FROM activity 
JOIN chemicals ON chemicals.bar_code = activity.bar_code 
JOIN users ON users.badge_number=activity.badge_number 
WHERE  (activity="login") 
    OR (activity="logout") 
   OR (activity LIKE "Admin:%") 
   OR (activity="tag") 
   OR (activity="untag") 
   OR (activity LIKE "check out%") 
   OR (activity LIKE "pour%") 
   OR (activity="check out (unscheduled)") 
   OR (activity="not poured after checking out") 
   OR (activity LIKE "invalid:%") 
ORDER BY time_stamp DESC

当我运行它时，它仅返回与 WHERE 的最后一个 OR 匹配的行(723 来自 867)。例如，我没有看到 activity="login"的任何行，但是 SELECT * FROM activity WHERE activity="login" 确实返回了一些行。

我做错了什么？ (如果那应该是“INNER JOIN”)？

更新

在我添加 Join 之前，我应该说这是有效的(对于复杂的 WHERE)(我是一个完全的 SQL 初学者)。

Answer 1

请研究INNER JOIN and LEFT JOIN之间的区别.

通过查看您的查询和基于名称的 Crystal 球注视以及与现实世界实体的关联，我可以看到以下内容:

当你的事件是“倒”的时候，可能会涉及到一些化学物质，这些化学物质会在条形码上成功加入。当它是“登录”时，我怀疑您是否会针对化学表获得成功的(INNER)JOIN。正如有人提到的，您真的想将它们变成 LEFT JOIN，例如

SELECT users.name, 
       from_unixtime(activity.time_stamp, "%D %b %Y  %l:%i:%S"), 
      activity.activity, 
      activity.rfid_tag, 
      chemicals.description 
FROM activity 
LEFT JOIN chemicals ON chemicals.bar_code = activity.bar_code 
LEFT JOIN users ON users.badge_number=activity.badge_number 
WHERE  (activity="login") 
   OR (activity="logout") 
   OR (activity LIKE "Admin:%") 
   OR (activity="tag") 
   OR (activity="untag") 
   OR (activity LIKE "check out%") 
   OR (activity LIKE "pour%") 
   OR (activity="check out (unscheduled)") 
   OR (activity="not poured after checking out") 
   OR (activity LIKE "invalid:%") 
ORDER BY time_stamp DESC