CODEWITHSUNDEEP
phpmysqlselectcount
ekashking
ekashking

Reputation: 447

MYSQL SELECT including COUNT within the same table with reference to specific column

So, I'm getting data from one table with a simple SELECT statement with SELECT COUNT included.

SELECT *, 
    (SELECT COUNT(`message_id`) 
    FROM `user_message` 
    WHERE `status_to` = 0
    ) AS `unread`  
FROM `user_message`

Here, unread counts for unread messages coming from other users. But that's not quite enough. What I want is to reference SELECT COUNT to a specific column, user_id, within the same table:

SELECT *, 
    (SELECT COUNT(`message_id`) 
    FROM `user_message` 
    WHERE `status_to` = 0 AND `user_message`.`user_id` = `user_message`.`user_id`
    ) AS `unread` 
FROM `user_message`

.. if that makes sense. My second statement disregards this part: AND user_message.user_id = user_message.user_id, and gives me the same result for each user_id.

What am I missing??

Upvotes: 1

Views: 171

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

You need to give different aliases to your table to get the related count

SELECT *, (
    SELECT COUNT(`message_id`) 
    FROM `user_message` b 
    WHERE `status_to` = 0 
    AND `a`.`user_id` = `b`.`user_id`
    ) AS `unread`
FROM `user_message` a

Upvotes: 1

Related Questions