using nested query result in another nested query

Question

SQL FIDDLE: http://sqlfiddle.com/#!2/5895f/5

I have groups, each groups(item_group) can have many items(item) and each item has one status entry per user (item_status). I am trying to retrieve the last item for each group that was not from yourself. Think of it as a message thread, you only want to see as a preview of that thread the last message that was sent but that wasn't from you.

This works fine with my query

  SELECT * FROM (
    SELECT it.id as group_id, i.sender_id as sender_id
    FROM item_group it
    JOIN item i ON (i.item_group_id = it.id)
    JOIN item_status s ON (s.item_id = i.id)
  ) as results
  WHERE results.sender_id != 2  
  GROUP BY results.group_id;

However the next step is I want to make sure the status of any items of my items was not 0 this is where my problem is. It says Unknown column 'results.group_id'

  SELECT * FROM (
    SELECT it.id as group_id, i.sender_id as sender_id
    FROM item_group it
    JOIN item i ON (i.item_group_id = it.id)
    JOIN item_status s ON (s.item_id = i.id)
  ) as results
  WHERE results.sender_id != 2
  AND (SELECT COUNT(*) = 0 
       FROM item_status s
       --- PROBLEM IS HERE results.group_id ---
       JOIN item i ON (i.item_group_id = results.group_id AND s.item_id = i.id) 
       ---------------------------------------- 
       WHERE s.user_id = 2 AND s.status = 0)
  GROUP BY results.group_id;



 CREATE TABLE item_group (
  id      int(10) PRIMARY KEY NOT NULL AUTO_INCREMENT);

  CREATE TABLE item (
  id             int(10) PRIMARY KEY NOT NULL AUTO_INCREMENT, 
  item_group_id  int(10) NOT NULL, 
  sender_id MEDIUMINT UNSIGNED NOT NULL);

  CREATE TABLE item_status (
  item_id     int(10) NOT NULL, 
  user_id     MEDIUMINT UNSIGNED NOT NULL, 
  status      int(10) NOT NULL);

  INSERT INTO item_group (ID) VALUES (1);
  INSERT INTO item (item_group_id, sender_id) VALUES (1, 1);
  INSERT INTO item (item_group_id, sender_id) VALUES (1, 2);
  INSERT INTO item_status (item_id, user_id, status) VALUES (1, 1, 1);
  INSERT INTO item_status (item_id, user_id, status) VALUES (1, 2, 1);
  INSERT INTO item_status (item_id, user_id, status) VALUES (2, 1, 1);
  INSERT INTO item_status (item_id, user_id, status) VALUES (2, 2, 1);

Answer 1

I would write this in the following way, without subqueries:

SELECT i.item_group_id as group_id, i.sender_id as sender_id
FROM item i 
JOIN item_status s ON (s.item_id = i.id)
LEFT OUTER JOIN (
    item i2 INNER JOIN item_status s2 
      ON (s2.item_id = i2.id AND s2.user_id = 2 AND s2.status = 0) 
) ON (i.item_group_id = i2.item_group_id)
WHERE i.sender_id != 2 AND i2.item_group_id IS NULL
GROUP BY group_id ORDER BY NULL;

You should also create the following indexes:

ALTER TABLE item_status 
    ADD KEY (user_id, status),
    ADD KEY (item_id);

I tested this query on MySQL 5.5.30. With the recommended indexes, the optimizer EXPLAIN report is as follows:

+----+-------------+-------+--------+-----------------+---------+---------+-----------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys   | key     | key_len | ref             | rows | Extra                                        |
+----+-------------+-------+--------+-----------------+---------+---------+-----------------+------+----------------------------------------------+
|  1 | SIMPLE      | i     | ALL    | PRIMARY         | NULL    | NULL    | NULL            |    2 | Using where; Using temporary                 |
|  1 | SIMPLE      | s2    | ref    | user_id,item_id | user_id | 7       | const,const     |    0 |                                              |
|  1 | SIMPLE      | i2    | eq_ref | PRIMARY         | PRIMARY | 4       | test.s2.item_id |    1 | Using where                                  |
|  1 | SIMPLE      | s     | ref    | item_id         | item_id | 4       | test.i.id       |    1 | Using index                                  |
+----+-------------+-------+--------+-----------------+---------+---------+-----------------+------+----------------------------------------------+

---

I thought of a quick solution for your followup question, how to get item groups for which all items have status 0.

SELECT i.item_group_id as group_id, i.sender_id as sender_id
FROM item i 
JOIN item_status s ON (s.item_id = i.id)
LEFT OUTER JOIN (
    item i2 INNER JOIN item_status s2 
      ON (s2.item_id = i2.id AND s2.user_id = 2 AND s2.status <> 0) 
) ON (i.item_group_id = i2.item_group_id)
WHERE i.sender_id != 2 AND i2.item_group_id IS NULL
GROUP BY group_id ORDER BY NULL;

This is almost identical to the above query, except I've changed = 0 to <> 0 in the join condition. So this means:

"try to find any non-zero status, and if you can't find any, then the OUTER JOIN will return null for all columns of the joined tables. When that happens, you've found an item group with all zero statuses."

PS: I haven't tested this one, just offering it as a quick solution.