MySQL - 仅当成员不在另一个列表中时才返回成员列表

Question

我尝试仅在成员不在另一个列表中时才创建成员列表。当另一个列表至少有一个成员时，查询将起作用(返回不在另一个列表中的成员(。但是，当另一个列表不包含任何成员(返回 NULL(时，则不返回任何值。

String selectQry = ("SELECT at_cub_details.cd_id, at_cub_details.cd_surname, at_cub_details.cd_first_name, " +
"at_cub_details.cd_archived " +
"FROM at_cub_details, at_account_group " + 
"WHERE at_account_group.acc_id = ? AND at_account_group.grp_id = at_cub_details.grp_id " +
"AND ( " +
" SELECT at_cub_details.cd_id " +
"FROM at_group, at_account_group group1, at_account_group group2, at_accounts " + 
"LEFT JOIN at_account_cub_association ON at_accounts.acc_id = at_account_cub_association.acc_id " +
"LEFT JOIN at_cub_details ON at_account_cub_association.cd_id = at_cub_details.cd_id " +
"WHERE (at_accounts.acc_id = ? " +
"AND (group1.acc_id = ?  " +
"AND group1.grp_id = group2.grp_id " +
"AND group2.acc_id = at_accounts.acc_id)) " +
"AND (group2.grp_id = at_group.grp_id) LIMIT 1) IS NOT NULL " +
"AND at_cub_details.cd_id NOT IN ( " +
" SELECT at_cub_details.cd_id " +
"FROM at_group, at_account_group group1, at_account_group group2, at_accounts " + 
"LEFT JOIN at_account_cub_association ON at_accounts.acc_id = at_account_cub_association.acc_id " +
"LEFT JOIN at_cub_details ON at_account_cub_association.cd_id = at_cub_details.cd_id " +
"WHERE (at_accounts.acc_id = ? " +
"AND (group1.acc_id = ?  " +
"AND group1.grp_id = group2.grp_id " +
"AND group2.acc_id = at_accounts.acc_id)) " +
"AND (group2.grp_id = at_group.grp_id)) " +
"ORDER BY at_cub_details.cd_surname, at_cub_details.cd_first_name;");

好的，为了简化第二个和第三个"SELECT"工作，第一个"SELECT"仅在第三个"SELECT"返回值时有效，而不是在返回"NULL"时起作用。最初我只有第三个"选择"。然后我添加了第二个"选择"以首先检查"NULL"。但是，这并没有纠正问题。所以：

这有效：

String selectQry = ("SELECT at_cub_details.cd_id, at_cub_details.cd_surname, at_cub_details.cd_first_name, " +
"at_cub_details.cd_archived " +
"FROM at_cub_details, at_account_group " + 
"WHERE at_account_group.acc_id = ? AND at_account_group.grp_id = at_cub_details.grp_id " +
"AND at_cub_details.cd_id NOT IN ( '20' ) " +
"ORDER BY at_cub_details.cd_surname, at_cub_details.cd_first_name;");

这不起作用：

String selectQry = ("SELECT at_cub_details.cd_id, at_cub_details.cd_surname, at_cub_details.cd_first_name, " +
"at_cub_details.cd_archived " +
"FROM at_cub_details, at_account_group " + 
"WHERE at_account_group.acc_id = ? AND at_account_group.grp_id = at_cub_details.grp_id " +
"AND at_cub_details.cd_id NOT IN ( NULL ) " +
"ORDER BY at_cub_details.cd_surname, at_cub_details.cd_first_name;");

至于你是否投了反对票，我真的不在乎，因为我这样做不是为了盈利和边走边学(我不是程序员(。

Answer 1

CodeRanch 的人非常乐于助人。答案是使用"IFNULL"在空时返回0，而不是检查空。

String selectQry = ("SELECT at_cub_details.cd_id, at_cub_details.cd_surname, at_cub_details.cd_first_name, " +
"at_cub_details.cd_archived " +
"FROM at_cub_details, at_account_group " + 
"WHERE at_account_group.acc_id = ? AND at_account_group.grp_id = at_cub_details.grp_id " +
"AND at_cub_details.cd_id NOT IN ( " +
"SELECT IFNULL (at_cub_details.cd_id, 0) " +
"FROM at_group, at_account_group group1, at_account_group group2, at_accounts " + 
"LEFT JOIN at_account_cub_association ON at_accounts.acc_id = at_account_cub_association.acc_id " +
"LEFT JOIN at_cub_details ON at_account_cub_association.cd_id = at_cub_details.cd_id " +
"WHERE (at_accounts.acc_id = ? " +
"AND (group1.acc_id = ?  " +
"AND group1.grp_id = group2.grp_id " +
"AND group2.acc_id = at_accounts.acc_id)) " +
"AND (group2.grp_id = at_group.grp_id)) " +
"ORDER BY at_cub_details.cd_surname, at_cub_details.cd_first_name;");

亲切问候

格林