如果不存在零值，则只会返回SQL记录

Question

这是我的MySQL数据库中的注册表。

SIGNUP 
id (int) 
customer (int) 
helper (int) 
eid (int) 
..plus more fields 

当我想让它返回数据库中的记录时，此查询返回0结果。情况是，SIGNUP表有一个记录，客户= 11，帮助= 0，批准= 0

如果助手字段填充的数字不是0，那么记录会返回，但如果它是0，那么那里是0条记录返回。如果我拿出第二条选择线（包含v.field1，v.field2等），那么它也可以工作。问题似乎是没有数据要从帮助列中提取，因此它不允许返回任何其他内容，但仍有其他字段要返回的数据。打开想法和所有帮助表示赞赏！非常感谢。

SELECT 
    u.id cId, u.first cFirst, u.email cEmail, u.username cUsername, 
    v.id hId, v.first hFirst, v.email hEmail, v.username hUsername, 
    s.customer, s.helper, s.eid EID, s.approved, 
    e.name, DATE_FORMAT(e.date, '%W, %b %e %Y %l:%i %p') date, e.summary, e.street, e.city, e.state, e.zip eZip 
FROM signup s 
INNER JOIN events e ON e.id = s.eid 
INNER JOIN users u ON u.id = s.customer 
INNER JOIN users v ON v.id = s.helper 
WHERE 
    (s.customer = 11 OR s.helper = 11) 
AND e.date > sysdate() 
ORDER BY e.date asc 

Answer 1

试试这个：

SELECT 
    u.id cId, u.first cFirst, u.email cEmail, u.username cUsername, 
    v.id hId, v.first hFirst, v.email hEmail, v.username hUsername, 
    s.customer, s.helper, s.eid EID, s.approved, 
    e.name, DATE_FORMAT(e.date, '%W, %b %e %Y %l:%i %p') date, e.summary, e.street, e.city, e.state, e.zip eZip 
FROM signup s 
INNER JOIN events e ON e.id = s.eid 
INNER JOIN users u ON u.id = s.customer 
left JOIN users v ON v.id = s.helper 
WHERE (s.customer = 11 OR s.helper = 11) 
AND e.date > sysdate() 
ORDER BY e.date asc 

Answer 2

如果我理解正确，您需要一个LEFT JOIN。这样，即使没有匹配的帮助器，您也可以获得所有行。

SELECT 
    u.id cId, u.first cFirst, u.email cEmail, u.username cUsername, 
    v.id hId, v.first hFirst, v.email hEmail, v.username hUsername, 
    s.customer, s.helper, s.eid EID, s.approved, 
    e.name, DATE_FORMAT(e.date, '%W, %b %e %Y %l:%i %p') date, e.summary, e.street, e.city, e.state, e.zip eZip 
FROM signup s 
INNER JOIN events e ON e.id = s.eid 
INNER JOIN users u ON u.id = s.customer 
LEFT JOIN users v ON v.id = s.helper 
WHERE 
    (s.customer = 11 OR s.helper = 11) 
AND e.date > sysdate() 
ORDER BY e.date asc