Is there way I can SELECT and JOIN COUNT different queries?

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

id	parent_id	sender_id	receiver_id	content	readed	sender_deleted	receiver_deleted	created
18	0	6	1	testab	0	0	0	2021-10-28 01:13:42
19	18	6	1	testcd	0	0	0	2021-10-28 01:14:55

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?

Answer

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:

id	parent_id	sender_id	receiver_id	content	readed	sender_deleted	receiver_deleted	created
18	0	6	1	testab	1	0	0	2021-10-28 01:13:42
19	18	6	1	testcd	1	0	0	2021-10-28 01:14:55
20	18	6	1	testde	0	0	0	2021-10-28 01:15:05
21	0	6	1	testfg	0	0	0	2021-10-28 02:34:11
22	21	6	1	testhi	0	0	0	2021-10-28 02:44:01

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

id	parent_id	content	unreaded_message
18	0	testab	0
19	18	testcd	0
20	18	testde	1
21	0	testfg	1
22	21	testhi	1

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

m_id	contents	unreaded_message
18	testde	1
21	testgf testhi	2

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 'rn' ) AS contents,
        SUM(CASE WHEN readed=0 THEN 1 ELSE 0 END) AS unreaded_message
FROM message
GROUP BY m_id;

Demo fiddle