The error I'm getting is:
Not unique table/alias: 'favourites'
I apologise for the reasonably intense query(!):
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
What am I doing wrong? I really can't understand it...
Thanks!
Jack
I guess that you only want to join with the favourites table once but with two conditions. If so use this:
LEFT JOIN favourites ON favourites.user_id = 10
AND favourites.code_id = code.id
If you do actually want to join with this table twice then you need to give it two different aliases:
LEFT JOIN favourites f1 ON f1.user_id = 10
LEFT JOIN favourites f2 ON f2.code_id = code.id
Then in the SELECT list you instead of favourites you need to use either f1 or f2 as appropriate.
精彩评论