0

I have two tables:

mysql> DESCRIBE swaps;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| id          | int(11)  | NO   | PRI | NULL    | auto_increment |
| user1_id    | int(11)  | NO   |     | NULL    |                |
| user2_id    | int(11)  | NO   |     | NULL    |                |
| hasto       | int(11)  | NO   |     | NULL    |                |
| requested   | datetime | NO   |     | NULL    |                |
| accepted    | datetime | YES  |     | NULL    |                |
| swapped1    | datetime | YES  |     | NULL    |                |
| swapped2    | datetime | YES  |     | NULL    |                |
| rejected    | datetime | YES  |     | NULL    |                |
| rejected_by | int(11)  | YES  |     | NULL    |                |
+-------------+----------+------+-----+---------+----------------+
mysql> DESCRIBE messages;
+-----------+----------+------+-----+-------------------+----------------+
| Field     | Type     | Null | Key | Default           | Extra          |
+-----------+----------+------+-----+-------------------+----------------+
| msg_id    | int(11)  | NO   | PRI | NULL              | auto_increment |
| sender_id | int(11)  | NO   |     | NULL              |                |
| msg       | text     | NO   |     | NULL              |                |
| msg_time  | datetime | NO   |     | CURRENT_TIMESTAMP |                |
| swap_id   | int(11)  | NO   |     | NULL              |                |
| seen      | datetime | YES  |     | NULL              |                |
+-----------+----------+------+-----+-------------------+----------------+

and query that I adjusted from this question 

SELECT s.*, m.*
FROM swaps as s
JOIN messages as m
ON (s.id= m.swap_id AND m.msg_time=
  (SELECT MAX(msg_time) FROM messages WHERE messages.swap_id = s.id));

as a result I single row for every swap and information about last sent message within this swap. I want to add the count of messages that have not jet been seen (m.seen IS NULL).

I tried different approaches but always get error. What I want is to add count of messages in corresponding swap with seen IS NULL to my result set. Would appreciate any suggestions.

Community
  • 1
  • 1
Kārlis Janisels
  • 1,265
  • 3
  • 18
  • 41

1 Answers1

0

You can add the count as a subquery:

SELECT s.*, m.*,
       (SELECT COUNT(*) FROM messages m2 WHERE m2.seen IS NULL) as seen_is_null
FROM swaps s JOIN
     messages m
     ON s.id= m.swap_id AND
        m.msg_time =(SELECT MAX(m2.msg_time) FROM messages m2 WHERE m2.swap_id = s.id));

It seems curious to be counting over all messages, but that is what the question asks for. You can, of course, introduce a correlation clause to count for a given swap or something else.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Yes this is great start but I get all unseen messages for every swap. I need to add condition WHERE swap_id = ... but I do not have the swap_id. It should be joined. – Kārlis Janisels Jan 12 '17 at 18:02
  • Oh I missed your comment under code. It seems that I have not asked the question propertly - I need that correlation clause for given swap – Kārlis Janisels Jan 12 '17 at 18:04