mysql - 简单的 MySQL 连接不起作用

Question

我正在尝试选择有关他们相应投票的评论和摘要统计信息。并非每条评论都有投票权，有投票权的评论可以超过 1 票。这是我的查询:

select
    `article-comments`.id,
    `article-comments`.user as user_id,
    concat(users.first_name, ' ', users.last_name) as name,
    users.job_title,
    users.company_name,
    avatar,
    `article-comments`.content,
    datediff(now(), `article-comments`.date_added) as diff,
    `article-comments`.date_added as date,
    count(`comment-votes`.id) as votes_count,
    sum(`comment-votes`.score) as votes_score
from
    `article-comments` left outer join `comment-votes` on `article-comments`.id = `comment-votes`.comment,
    users
where
    `article-comments`.status = 1
 && `article-comments`.article = $resource_id
 && `article-comments`.user = users.id
 && `comment-votes`.status = 1
order by
    `article-comments`.id asc

它在没有连接的情况下工作得很好，但只返回 1 行。

有什么想法吗？

Answer 1

尝试:

select
                `article-comments`.id,
                `article-comments`.user as user_id,
                concat(users.first_name, ' ', users.last_name) as name,
                users.job_title,
                users.company_name,
                avatar,
                `article-comments`.content,
                datediff(now(), `article-comments`.date_added) as diff,
                `article-comments`.date_added as date,
                count(`comment-votes`.id) as votes_count,
                sum(`comment-votes`.score) as votes_score
            from
                `article-comments` left join `comment-votes` on (`article-comments`.id = `comment-votes`.comment),
                users
            where
                `article-comments`.status = 1
             && `article-comments`.$table = $resource_id
             && `article-comments`.user = users.id
            order by
                `article-comments`.id asc