I'm using MYSQL and I have a message table like below.

I'm trying to combine two queries in one. Selecting and count where readed value is 0.

This query is selecting query.

SELECT * FROM message 
    WHERE (sender_id = 1 OR receiver_id = 1) AND (id = 18 OR parent_id = 18);

And this query is counting query.

SELECT COUNT(id) FROM message 
     WHERE (sender_id = 1 OR receiver_id = 1) 
       AND (id = 18 OR parent_id = 18) AND (readed = 0);

I'm trying to combine these two using LEFT JOIN.

SELECT a.id, a.parent_id, a.content, COUNT(b.id) AS unreaded_message 
  FROM message a 
  LEFT JOIN message c ON a.id = c.id AND (readed = 0) 
 GROUP BY A.ID, A.Date 
 ORDER BY a.id;

But I'm getting error like below.

ERROR 1054 (42S22): Unknown column 'b.id' in 'field list'

Is there way I can left join count query?

CodePudding user response：

You are using in the query B alias in the "COUNT" satement. You need to use A or C alias like these example:

SELECT a.id, a.parent_id, a.content, COUNT(c.id) AS unreaded_message 
FROM message a 
LEFT JOIN message c ON a.id = c.id AND (readed = 0) 
GROUP BY A.ID, A.Date 
ORDER BY a.id;

CodePudding user response：

I notice these issues when testing the query:

1. The incorrect alias of b.id for the COUNT(). That's where the error is indicating.
2. The non-existing A.Date from table message. Based on your table sample, you don't have a column named as Date. Instead you have a column named created that stores date time.
3. The query itself is incompatible with sql_mode=only_full_group_by. Since MySQL v5.7.5, this setting is on by default, with a very good reason of - "if the only_full_group_by mode is turned off the server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic, which is probably not what you want (refer: "MySQL Handling of Group By" docs.

Now, if I'm not mistaken, content stores the text messages, correct? And since id=19 parent is id=18, it should be in the same row, with the total count of unread message is 2. I'm not sure if that's what you're really looking for but I'll post two queries; one is to address your current issue and another to suggest that it maybe what you're looking for.

For your current issue, you can achieve the desired result without self-join, and instead of using COUNT(), you can use SUM() with CASE expression like below:

SELECT id, parent_id, content, 
      SUM(CASE WHEN readed=0 THEN 1 ELSE 0 END) AS unreaded_message
FROM message
GROUP BY id, parent_id, content;

COUNT() will take every row in the query result depending on your conditions. The replacement of SUM() with CASE expression here is just telling the query to:

SUM(CASE WHEN readed=0 THEN 1 ELSE 0 END) AS unreaded_message
   -- if the readed column is 0 (zero) then give it 1, else give it 0 then add them up.

The second suggestion I have is this, let's assume you have more rows in the table like this:

With id of 18,19 both have readed=1, the query you have and the one I suggested above will return result like this:

But I have a feeling that you probably want something like this:

If that's a possibility, then you can just run this query:

SELECT CASE WHEN parent_id=0 THEN id ELSE parent_id END AS m_id, 
        GROUP_CONCAT(CASE WHEN readed=0 
                          THEN content ELSE '' END 
                      ORDER BY readed, id SEPARATOR '\r\n' ) AS contents,
        SUM(CASE WHEN readed=0 THEN 1 ELSE 0 END) AS unreaded_message
FROM message
GROUP BY m_id;

Demo fiddle