Mysql - 无法选择表A中存在且表B、C、D中不存在的记录

Question

我需要选择users表中存在但groups_members、groups_members_unapproved和groups_invitations表中不存在的记录。

$_POST['msg_id'] 包含多个用户 ID，我不知道如何在 3 个子查询中使用 implode 语句，所以我将其替换为 ?? ？ - 问号。

谢谢。

$result = mysql_query("SELECT id FROM users
WHERE id IN (". implode(', ', $_POST['msg_id']).")
AND id NOT IN (SELECT memberid FROM groups_members where memberid='???' and groupid='$cid')
AND id NOT IN (SELECT memberid FROM groups_members_unapproved where memberid='???' and groupid='$cid')
AND id NOT IN (SELECT invited FROM groups_invitations where invited = '???' and groupid='$cid')",$gb1) or die('Error');

Answer 1

最简单的方法是将 ??? 留在子查询中，因为那里不需要它们；

SELECT id FROM users WHERE id IN (...)
AND id NOT IN 
  (SELECT memberid FROM groups_members WHERE groupid='$cid')
AND id NOT IN 
  (SELECT memberid FROM groups_members_unapproved WHERE groupid='$cid')
AND id NOT IN 
  (SELECT invited FROM groups_invitations WHERE groupid='$cid')

SQLfiddle demo .