MySQL SELECT COUNT from one table and ID from another?

Question

I'm trying to query information from two different tables, but I'm not figuring out how to do it best. As a disclaimer, I'm still learning MySQL/PHP, and I don't have control over the tables as they're set up - I'm trying to work with what I've got, since I can't add/change the tables. Below are the tables and the relevant attributes:

Table(attribute1, attribute2, ...);
------------------------------------
reports(id, reporter_id, added)
report_comments(comment_id, report_id, comment_text, commenter_id)

The reporter_id refers to the user who filed a report, and commenter_id is not the same person as reporter_id.

I want to get a count of how many report comments have, for example, the word "incorrect" in comment_text, for each reporter_id. I then want to make a table that shows each reporter's ID and the number of comments that are associated with that reporter's reports since "1383359439" (timestamp).

So far, I've not been very successful. My current query looks like this:

SELECT r.id, r.reporter_id, 
    (SELECT COUNT(*) FROM report_comments WHERE comment_text LIKE '%incorrect%' AND report_id = r.id) AS comments
FROM reports AS r
LEFT JOIN report_comments AS rc ON r.id = rc.report_id
WHERE r.added > 1383359439
GROUP BY r.reporter_id;

The resulting page, when I set the HTML table to list "reporter_id" followed by "comments", gives everyone who has filed a report since the time listed, but the count is either "0" or "1", with any reporter who has had "incorrect" in any report comment getting a "1" and those without "incorrect" getting "0":

Reporter1 | 0
Reporter2 | 1
Reporter3 | 0
Reporter4 | 1
Reporter5 | 1

The thing is, some reporters have had several comments with "incorrect" in them, and I want to get a count of each, and ONLY for those reporters (not ones who've never had an "incorrect" comment). For example:

Reporter2 | 2
Reporter4 | 17
Reporter5 | 3

I'm clearly missing something - what am I doing wrong?

To filter out reporters with 0 comments, use HAVING clause. To clarify - you want to get number of comments to the reports after particular time that contains particular keyword and group it by reporters? — fracz
– fracz, Commented Nov 19, 2013 at 22:07

Mike Brant · Accepted Answer · 2013-11-21 16:58:56Z

2

You need to utilize grouping for this.

SELECT
  r.reporter_id AS `reporter_id`,
  COUNT(rc.report_id) AS `incorrect_count`
FROM reports AS r
INNER JOIN report_comments AS rc
  ON r.id = rc.report_id
WHERE rc.comment_text LIKE '%incorrect%'
AND r.added > ?
GROUP BY `reporter_id`

Here ? represents the timestamp you are trying to compare against.

To answer your follow-up question, there are a couple of ways to do this. I might suggest use of SUM() in conjunction with CASE like this:

SELECT
  r.reporter_id AS `reporter_id`,
  SUM(
    CASE WHEN rc.comment_text LIKE '%incorrect%'
      THEN 1
    ELSE 0
    END CASE
  ) AS `incorrect_count`,
  SUM(
    CASE WHEN rc.comment_text LIKE '%fake%'
      THEN 2
    ELSE 0
    END CASE
  ) AS `fake_count`,     
FROM reports AS r
INNER JOIN report_comments AS rc
  ON r.id = rc.report_id
WHERE
  rc.comment_text LIKE '%incorrect%'
  OR rc.comment_text LIKE '%fake%'
AND r.added > ?
GROUP BY `reporter_id`

edited Nov 21, 2013 at 16:58

answered Nov 19, 2013 at 22:08

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dion Over a year ago

Thank you, this worked perfectly. I have a followup question that slightly changes what I have to collect from the query - if it would be better to create a new question, I can do so: In addition to "incorrect" comments, there are comments with the word "fake" - these should count double (like demerits: incorrect reports are 1 demerit, fake reports are 2 demerits). Is there a way to gather that information within the same query, so that the total number sums up how many "demerits" the reporter has?

Mike Brant Over a year ago

@Dion There are a number of ways to tackle this problem. I have updated answer with one suggestion.

Dion Over a year ago

Thanks. Just as a note, it didn't work for the longest time, until I realized each CASE was missing the END. Now it works great!

Mike Brant Over a year ago

@Dion Yes bad oversight on my part. I have edited answer to reflect this.

Andrei B · Accepted Answer · 2013-11-19 22:08:42Z

1

It's something like this:

SELECT r.reporter_id, COUNT(*) comments
FROM reports AS r
    INNER JOIN report_comments AS rc ON r.id = rc.report_id
WHERE r.added > 1383359439
    AND comment_text LIKE '%incorrect%'
GROUP BY r.reporter_id;

I removed r.id since it doesn't make sense to have in this case as one reporter can have many reports (so multiple r.id).

answered Nov 19, 2013 at 22:08

Andrei B

2,76219 silver badges12 bronze badges

Comments

Marco · Accepted Answer · 2013-11-20 10:03:20Z

1

You could try

SELECT r.id, COUNT(c.id) tot
FROM reports r INNER JOIN report_comments
  ON r.id = c.report_id
  AND c.comment_text LIKE '%incorrect%'
  AND r.added > 1383359439
GROUP BY r.reporter_id

edited Nov 20, 2013 at 10:03

answered Nov 19, 2013 at 22:07

Marco

57.7k15 gold badges135 silver badges159 bronze badges

2 Comments

Mike Brant Over a year ago

You really should use WHERE clause for filtering criteria as there is no join being performed. Also, there is no need for HAVING here as the INNER JOIN will eliminate all records from report_comments that do not match the filter cireteria

Marco Over a year ago

@MikeBrant: I think that sql engine converts my syntax and the one you suggest in the same way...

Collectives™ on Stack Overflow

MySQL SELECT COUNT from one table and ID from another?

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related