下面的查询将返回所有"blocker"用户和所有"blocked"用户。相当直接的查询。
$stmt=$db->prepare('SELECT blocker,blocked FROM list_blocked
WHERE (blocked = :username AND blocker <> :username)
OR (blocker = :username AND blocked <> :username)');
$stmt->bindParam(':username', $username);
$stmt->execute();
下一个查询将返回成员表中除了当前$username之外的所有用户名。
$query = $db->prepare("SELECT username FROM members WHERE username <> :username");
$query->bindValue(':user', $username;
$query->execute();
$row = $query->fetchAll();
我需要的是一种从第一个查询的结果中获得所有值的方法，不包括活动的$username，然后从第二个查询中排除所有这些结果。
例如:
Example results of first query:
jim blocked joe
larry blocked joe
steve blocked joe
joe blocked tony
jack blocked joe
Those results applied to the second query would look something like:
$query = $db->prepare("SELECT username FROM members WHERE username <> :username
AND username <> jim
AND username <> larry
AND username <> steve
AND username <> tony
AND username <> jack");
我该如何实现这样的目标?
更新:
所以我试图使用子查询，但它一直返回"操作数应包含1列(s)"
SELECT username FROM members WHERE username NOT IN (
SELECT blocker,blocked FROM list_blocked
WHERE (blocked = 'viraladmin' AND blocker <> 'viraladmin')
OR (blocker = 'viraladmin' AND blocked <> 'viraladmin')
)
我尝试使用内部连接来实现这一点，但是结果总是返回空，我认为这是因为实际上没有任何东西可以连接。
SELECT members.username, list_blocked.blocker, list_blocked.blocked
FROM members
INNER JOIN list_blocked
ON members.username = list_blocked.blocker
AND members.username = list_blocked.blocked
WHERE members.username <> 'username'
AND (list_blocked.blocked = 'username' AND list_blocked.blocker <> 'username')
OR (list_blocked.blocker = 'username' AND list_blocked.blocked <> 'username')
您可以使用子查询完成此操作:
SELECT username FROM members WHERE username NOT IN (
SELECT blocker,blocked FROM list_blocked
WHERE (blocked = :username AND blocker <> :username)
OR (blocker = :username AND blocked <> :username)
)
Edited -要适合SQL查询，您需要在子查询中进行联合，以便结果返回不超过一列。:
SELECT username FROM members WHERE username NOT IN (
SELECT blocker as username FROM list_blocked
WHERE (blocked = :username AND blocker <> :username)
OR (blocker = :username AND blocked <> :username)
UNION
SELECT blocked as username FROM list_blocked
WHERE (blocked = :username AND blocker <> :username)
OR (blocker = :username AND blocked <> :username)
)
您也可以使用LEFT JOIN - NULL模式。
SELECT username
FROM members AS m
LEFT JOIN list_blocked AS b
ON m.username IN (b.blocker, b.blocked)
AND ((blocked = :username AND blocker <> :username)
OR (blocker = :username AND blocked <> :username))
WHERE b.blocked IS NULL
只有当value不存在