MySQL Query - SELECT DISTINCT ...（WHERE）问题

Question

正在苦苦寻找这个MySQL查询。当我拿出WHERE子句时，所有结果都会显示给用户。所以这不是问题的PHP输出。我很确定它在WHERE子句中，但我一直被卡住了很长时间，无法解决。

$test = $db->query 
("SELECT DISTINCT(a.id), a.name 
FROM test AS a LEFT JOIN(testusers AS b,testclasses AS c, userclasses AS d) 
ON (c.classID = d.classID AND a.id = b.testID) 
WHERE d.userID='" . $_SESSION["id"] . "' OR b.userID= '" . $_SESSION["id"] . "' 
GROUP BY a.id"); 

感谢您的帮助！

Answer 1

您不应在WHERE子句中引用来自LEFT JOINed表的列（例如，b.userID）。当你这样做时，你隐式地把你的JOIN变成一个INNER JOIN。使这些测试成为JOIN条件的一部分。

不这样做：

/* Wrong */ 
SELECT * 
    FROM test a 
     LEFT JOIN test2users b 
      ON a.id = b.testid 
    WHERE b.userID = 123 

做到这一点，而不是：

/* Right */ 
SELECT * 
    FROM test a 
     LEFT JOIN test2users b 
      ON a.id = b.testid 
       AND b.userID = 123 

Answer 2

我已经清理了参加陈述和删除不必要的DISTINCT（）。这给你什么？

SELECT a.id, a.name 
FROM " . $config["table_prefix"] . "test AS a 
LEFT JOIN " . $config["table_prefix"] . "test2users AS b 
    ON a.id = b.testID 
    AND b.userID= '" . $_SESSION["id"] . "' 
LEFT JOIN " . $config["table_prefix"] . "test2classes AS c 
LEFT JOIN " . $config["table_prefix"] . "test2classes AS d 
    ON c.classID = d.classID 
    AND d.userID='" . $_SESSION["id"] . "' 
GROUP BY a.id