CODEWITHSUNDEEP
mysql
Devesh Agrawal
Devesh Agrawal

Reputation: 9212

Mysql query to fetch unread response count

I have three MYSQL tables

Requests -  request_id, request_msg, user_id, request_datetime
Responses - response_id, request_id, response_msg, user_id, response_datetime, readflag
Users-      user_id, user_email, user_name

readflag = 0 or 1

I want to fetch the all requests of a user where at-least 1 response is unread for this request, total response count and unread response count for this request.

Upvotes: 0

Views: 395

Answers (2)

Zane Bien
Zane Bien

Reputation: 23125

Try this:

SELECT 
    a.request_id,
    COUNT(*) AS total_response_count,
    COUNT(b.request_id) AS unread_response_count
FROM
    Requests a
LEFT JOIN
    Responses b ON a.request_id = b.request_id AND b.readflag = 0
GROUP BY
    a.request_id
HAVING 
    COUNT(b.request_id) > 0

Here, we LEFT JOIN the Responses table on the relationship and also where messages are unread (b.readflag = 0). This makes all rows with read responses have a NULL value for b.response_id. Since COUNT() does not factor NULLs into its calculations, we filter only requests that have at least 1 unread response (HAVING COUNT(b.request_id) > 0).

Because we LEFT JOINed, all responses are retained, read or unread... just all read rows will contain NULL for b.request_id. COUNT(*) essentially counts on the existence of a row, so that gives us the total count of messages. Then COUNT(b.request_id) gives us the count of unread messages as it only factors non-NULL values into its calculation.

Upvotes: 2

TylerH4
TylerH4

Reputation: 473

Try something like this:

SELECT COUNT(`res`.`response_id`) AS `total`, COUNT(`res`.`readflag`='0') AS `unread`
FROM `Requests` AS `req`
LEFT JOIN `Responses` AS `res` ON `req`.`request_id`=`res`.`request_id`
WHERE `unread`>1 AND `req`.`user_id`='[PLACE_USER_ID_HERE]'

Upvotes: 0

Related Questions