mysql - 4 个表的 3 个 INNER JOIN，查询中某些值可能为空，也可能不为空

Question

在我网站的一个页面上，我在 4 个表(登录、统计、投票和公告)上使用内部联接，如下所示:

$login_user = mysqli_real_escape_string($cxn, $_SESSION['user']);

$sqlAll = mysqli_fetch_assoc(mysqli_query($cxn, 
    "SELECT login.picture, login.statement,
    stats.wealth,
    MAX(poll.polltime) AS pollunixtime,
    MAX(announce.announcetime) AS announceunixtime 
    FROM login 
    INNER JOIN stats ON login.user = stats.user 
    INNER JOIN poll ON login.user = poll.user 
    INNER JOIN announce ON login.user = announce.source 
    WHERE (login.user = '$login_user' AND announce.announcetype = 'feedback')"));

只要表中至少有一行announce 且表中的一行poll 已为该用户填充，此内部联接就可以正常工作，例如，此表单输入的值定义为:

<form id="myForm"><input type="hidden" name="wealth" value="<?= $sqlAll['wealth'] ?>"></form>//"View Page Source" shows this value is defined and shows up as value="number"

但是，我注意到，当用户的任一表都不存在行时，查询会失败，因为查询中的某些值未定义。例如，如果两个表都没有行，则此表单输入的值未定义:

<form id="myForm"><input type="hidden" name="wealth" value="<?= $sqlAll['wealth'] ?>"></form>//"View Page Source" shows this value is undefined and shows up as value=""

我尝试以多种方式将 CASE 引入我的查询中，例如:

$sqlAll = mysqli_fetch_assoc(mysqli_query($cxn, 
    "SELECT login.picture, login.statement,
     stats.wealth,
     MAX(poll.polltime) AS pollunixtime,
     MAX(announce.announcetime) AS announceunixtime
     FROM login
     INNER JOIN stats ON login.user = stats.user
     INNER JOIN poll ON CASE WHEN poll.polltime IS NOT NULL THEN login.user = poll.user END 
     INNER JOIN announce ON CASE WHEN announce.announcetime IS NOT NULL THEN login.user = announce.source END 
     WHERE (login.user = '$login_user' AND announce.announcetype = 'feedback')"));

由于polltime和announcetime都是INT类型，所以我也尝试使用“">”，如下:

$sqlAll = mysqli_fetch_assoc(mysqli_query($cxn, 
    "SELECT login.picture, login.statement,
    stats.wealth,
    MAX(poll.polltime) AS pollunixtime,
    MAX(announce.announcetime) AS announceunixtime 
    FROM login
    INNER JOIN stats ON login.user = stats.user 
    INNER JOIN poll ON CASE WHEN poll.polltime > 0 THEN login.user = poll.user END    
    INNER JOIN announce ON CASE WHEN announce.announcetime > 0 THEN login.user = announce.source END 
    WHERE (login.user = '$login_user' AND announce.announcetype = 'feedback')"));

我还尝试了将 CASE 语句与 poll 和 announce 一起放置的变体，如下所示:

$sqlAll = mysqli_fetch_assoc(mysqli_query($cxn, 
    "SELECT login.picture, login.statement, 
     stats.wealth, 
     CASE WHEN MAX(poll.polltime) IS NOT NULL THEN 'pollunixtime' END, 
     CASE WHEN MAX(announce.announcetime) IS NOT NULL THEN 'announceunixtime' END 
     FROM login 
     INNER JOIN stats ON login.user = stats.user 
     INNER JOIN poll ON login.user = poll.user 
     INNER JOIN announce ON login.user = announce.source 
     WHERE (login.user = '$login_user' AND announce.announcetype = 'feedback')"));

我尝试过的任何变体都不起作用。基本上，我需要一个条件内部连接，以便如果给定用户同时存在 polltime 和announcetime，则执行第二个和第三个内部连接，否则不执行第二个和第三个内部连接。这可能吗？

请注意，我已经有了一个 hack 解决方案，在该系统中，该解决方案涉及将上面的一个查询分解为 3 个单独的查询，并使用 ISSET 测试给定用户是否存在 polltime 和announcetime。我想做得比这个解决方案更好!

Answer 1

您可以通过使用 LEFT JOIN 而不是 INNER JOIN 来完成与您所要求的有些类似的操作。