如果行不存在，则选择数据

mxg2im7a 于 2021-06-25 发布在 Mysql

关注(0)|答案(2)|浏览(311)

我有几张table是连在一起的。我试图只选择那些没有插入行的。
例子一。我选择这样的用户：

SELECT  *
FROM user_accounts AS ua
    INNER JOIN users AS u ON u.id = ua.user_id
    INNER JOIN account_profile_entries AS ape ON ape.user_account_id = ua.id
    INNER JOIN profile_entries AS pe ON pe.id = ape.profile_entry_id
WHERE pe.profile_field_id = 1227 AND pe.value_tinyint = 0;

在这个查询中，我有一个profile字段，它有1227，它的值是0。一切正常。
例二。我也做了类似的查询，但是这个时间值\u tinyint=0：

SELECT  *
FROM user_accounts AS ua
    INNER JOIN users AS u ON u.id = ua.user_id
    INNER JOIN account_profile_entries AS ape ON ape.user_account_id = ua.id
    INNER JOIN profile_entries AS pe ON pe.id = ape.profile_entry_id
WHERE pe.profile_field_id = 1227 AND pe.value_tinyint = 1;

在这种情况下也可以。
我被困的地方：
在pe.profile\u field\u id=1227中没有条目（行）时，当我需要选择用户\u帐户时，我被卡住了这是我迄今为止尝试的：

SELECT *
FROM user_accounts AS ua
WHERE ua.id NOT IN (SELECT u.email AS UserEmail
FROM user_accounts AS ua
    INNER JOIN users AS u ON u.id = ua.user_id
    INNER JOIN account_profile_entries AS ape ON ape.user_account_id = ua.id
    INNER JOIN profile_entries AS pe ON pe.id = ape.profile_entry_id
 WHERE pe.profile_field_id = 1227)

但是这个查询也返回那些拥有条目1227的用户。
如何修改该查询以仅返回那些在profile\u entries表中没有条目的用户？

mysql

来源：https://stackoverflow.com/questions/49902550/select-data-if-a-row-does-not-exists

2条答案

按热度按时间

g6ll5ycj1#

看起来您选择的是电子邮件而不是id。请将查询更改为：

SELECT *
FROM user_accounts AS ua
WHERE ua.id NOT IN (SELECT u.id  
FROM user_accounts AS ua
    INNER JOIN users AS u ON u.id = ua.user_id
    INNER JOIN account_profile_entries AS ape ON ape.user_account_id = ua.id
    INNER JOIN profile_entries AS pe ON pe.id = ape.profile_entry_id
WHERE pe.profile_field_id = 1227)

赞(0）回复(0）举报 2021-06-26

yeotifhr2#

您需要对不知道行是否存在的表使用左联接操作：

SELECT *
  FROM user_accounts AS ua
 WHERE ua.id NOT IN 
         (SELECT u.id 
            FROM user_accounts AS ua
                 INNER JOIN users AS u ON u.id = ua.user_id
                 INNER JOIN account_profile_entries AS ape 
                    ON ape.user_account_id = ua.id
                 LEFT JOIN profile_entries AS pe 
                    ON pe.id = ape.profile_entry_id
                       AND pe.profile_field_id = 1227)

注意，我还移动了 pe.profile_field_id = 1227 因为如果使用左联接操作并且左联接表上有联接，则if不再是左联接。
您还需要将子查询上的列更改为id，因为它是您要比较的对象。

赞(0）回复(0）举报 2021-06-25

我来回答

如果行不存在，则选择数据

2条答案

相关问题

热门标签

最新问答