mysql - INNER JOIN + LEFT JOIN(双重过滤)

Question

我正在尝试过滤我的表，其中用户在一个表中有一行，而在另一个表中没有行。这是我的表结构:

这里有一个 SQL Fiddle: http://sqlfiddle.com/#!9/6e27ed/2

CREATE TABLE users (
   user_id INT(11) AUTO_INCREMENT,
   name VARCHAR(25),
   PRIMARY KEY(user_id)
);

CREATE TABLE photos (
   photo_id INT(11) AUTO_INCREMENT,
   user_id INT(11) NOT NULL,
   PRIMARY KEY(photo_id)
);

CREATE TABLE blocked (
   user_id INT(11) NOT NULL,
   blocked_id INT(11) NOT NULL
);

INSERT INTO users (name) 
VALUES ('returned'), ('not returned'), ('not returned'), ('not returned');

INSERT INTO photos (user_id) 
VALUES (1), (2);

INSERT INTO blocked (user_id, blocked_id) 
VALUES (4, 2);

这是我正在尝试的查询:

SELECT u.*, min(p.photo_id)
FROM users u
INNER JOIN photos p using(user_id)
LEFT JOIN blocked b ON b.user_id = 4 AND b.blocked_id = u.user_id 
WHERE u.user_id != 4
GROUP BY u.user_id
LIMIT 9;

示例数据非常清楚结果应该是什么，因为“名称”字段填充了 “已返回”、“未返回” 在这个示例中，user-id 2 仍然是返回，但他应该被 LEFT JOIN 删除，因为用户 ID 4 在阻塞表的阻塞 ID 字段中有用户 ID 2。

表中的预期结果:

+---------+----------+-----------------+
| user_id |   name   | min(p.photo_id) |
+---------+----------+-----------------+
|       1 | returned |               1 |
+---------+----------+-----------------+

收到查询结果:

+---------+--------------+-----------------+
| user_id |     name     | min(p.photo_id) |
+---------+--------------+-----------------+
|       1 | returned     |               1 |
|       2 | not returned |               2 |
+---------+--------------+-----------------+

Answer 1

很难回答这个问题，因为我看到您正在比较 blocked_id=user_id，尽管在 blocked 表中您也有一个 user_id 列。只有你自己知道。

但请考虑以下内容

SELECT u.*, min(p.photo_id),b.*
FROM users u
INNER JOIN photos p using(user_id)
LEFT JOIN blocked b ON b.user_id = 4 AND b.blocked_id = u.user_id 
WHERE u.user_id != 4 and b.user_id is null
GROUP BY u.user_id
LIMIT 9;

+---------+----------+-----------------+---------+------------+
| user_id | name     | min(p.photo_id) | user_id | blocked_id |
+---------+----------+-----------------+---------+------------+
|       1 | returned |               1 |    NULL |       NULL |
+---------+----------+-----------------+---------+------------+

它通过显示添加为第 4 列和第 5 列的 b.* 列来打开声纳。并且稍微弄乱了 where 子句。

编辑:

为生产清理

SELECT u.*, min(p.photo_id)
FROM users u
INNER JOIN photos p using(user_id)
LEFT JOIN blocked b ON b.user_id = 4 AND b.blocked_id = u.user_id 
WHERE u.user_id != 4 and b.user_id is null
GROUP BY u.user_id
LIMIT 9;