MySQL:不是唯一的表/别名: 'favourites'

Question

我得到的错误是:

Not unique table/alias: 'favourites'

对于相当激烈的查询(!)，我深表歉意:

    SELECT code.id AS codeid, code.featured AS featured, code.title AS codetitle, code.summary AS codesummary, code.author AS codeauthor, code.date, code.challengeid,
    ratingItems.*, FORMAT((ratingItems.totalPoints / ratingItems.totalVotes), 1) AS rating,
    code_tags.*, tags.*,
    users.firstname AS authorname, users.id AS authorid,
    GROUP_CONCAT(tags.tag SEPARATOR ', ') AS taggroup,
    COUNT(DISTINCT comments.codeid) AS commentcount,
    favourites.id, favourites.code_id, favourites.user_id

    FROM (code)
    JOIN code_tags ON code_tags.code_id = code.id
    JOIN tags ON tags.id = code_tags.tag_id
    JOIN users ON users.id = code.author
    LEFT JOIN comments ON comments.codeid = code.id
    LEFT JOIN ratingItems ON uniqueName = code.id
    LEFT JOIN favourites ON favourites.user_id = 10
    LEFT JOIN favourites ON favourites.code_id = code.id

    GROUP BY code_id
    ORDER BY date desc

我做错了什么？实在看不懂...

谢谢!

jack

Answer 1

我猜您只想加入收藏夹表一次，但有两个条件。如果是这样使用这个:

LEFT JOIN favourites ON  favourites.user_id = 10
                     AND favourites.code_id = code.id

如果你真的想加入这个表两次，那么你需要给它两个不同的别名:

LEFT JOIN favourites f1 ON f1.user_id = 10
LEFT JOIN favourites f2 ON f2.code_id = code.id

然后在 SELECT 列表中，您需要根据需要使用 f1 或 f2 而不是收藏夹。