MySQL Query - SELECT DISTINCT... (WHERE) issue

Struggling with this MySQL query. When I take out th开发者_如何学Pythone WHERE clause all the results appear for the user. So it isn't the php output that is the issue. I'm pretty sure it's in the WHERE clause, but I've been stuck for ages and can't work it out.

$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");

Thanks for your help!

You should not reference columns from a LEFT JOINed table (e.g., b.userID) in the WHERE clause. When you do, you implicitly turn your JOIN into an INNER JOIN. Make those tests part of the JOIN condition instead.

DON'T do this:

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

DO this instead:

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

I've cleaned up the join statements and removed the unnecessary DISTINCT(). What does this give you?

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