CODEWITHSUNDEEP
mysqljoingroup-by
ShoeLace1291
ShoeLace1291

Reputation: 4698

Why am I getting an error while using group by with a left join?

I am trying to develop a feature for my admin panel that gets comments based on any "flags" they may have. I want t fetch results from the comments table, but only comments that have associated rows in the flags table. I then want to order the results by the total number of flags that the comments have.

These are my table structures:

CREATE TABLE IF NOT EXISTS `article_comments` (
  `comment_id` int(15) NOT NULL AUTO_INCREMENT,
  `author_id` int(15) NOT NULL,
  `article_id` int(15) NOT NULL,
  `modifier_id` int(15) NOT NULL,
  `content` varchar(255) NOT NULL,
  `date_posted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `date_modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`comment_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

CREATE TABLE IF NOT EXISTS `article_comment_flags` (
  `flag_id` int(15) NOT NULL AUTO_INCREMENT,
  `comment_id` int(15) NOT NULL,
  `member_id` int(15) NOT NULL,
  `date_flagged` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`flag_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

This is the current query that I am using:

    SELECT
        c.comment_id AS comment_id, c.article_id,
        f.flag_id, f.comment_id, f.member_id, f.date_flagged, COUNT(f.flag_id) AS total_flags
    FROM article_comments AS c
    LEFT JOIN article_comment_flags AS f ON (c.comment_id = f.comment_id)
    GROUP BY comment_id
    ORDER BY total_flags DESC
    LIMIT 5

This is the current SQL error that I am getting:

Column 'comment_id' in group statement is ambiguous

Anyone have any ideas?

Upvotes: 1

Views: 411

Answers (2)

ger
ger

Reputation: 1

Column 'comment_id' in group statement is ambiguous:

SELECT
c.comment_id AS comment_id,
f.comment_id
FROM article_comments AS c
LEFT JOIN article_comment_flags AS f ON (c.comment_id = f.comment_id)
GROUP BY comment_id
  • They are equal, so you need only one of them, take the one from the left side because it is always there, so delete f.comment_id from the select part.
  • You can write USING (comment_id) instead of ON(..), then you only get this column once.
  • Explicitely tell, wich column to GROUP BY, here best use c.comment_id from the left side

Upvotes: 0

marian0
marian0

Reputation: 3327

You have to point by which comment_id it will be grouped. So you have to change line:

GROUP BY comment_id

into

GROUP BY c.comment_id

Upvotes: 2

Related Questions