我对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(723 from 867)匹配的行。例如,我没有看到activity=“login”的任何行,但是
SELECT * FROM activity WHERE activity="login"
确实返回了一些行。有人能告诉我我做错了什么吗?(如果这应该是“内部连接”)?
[更新]在我添加连接之前,我应该说这是可行的(在复杂的地方)(我是一个完整的SQL n00b)
最佳答案
请研究INNER JOIN and LEFT JOIN之间的区别。
通过查看您的查询和基于名称和与真实世界实体相关联的水晶球凝视,我可以看到以下内容:
当你的活动是“倒”,它可能涉及一些化学品,这将成功地加入条码。当它是“login”时,我怀疑您是否能成功(内部)连接到chemical表。正如有人提到的,你真的想把它们变成左连接。
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