sql返回列表中的where,而不是任何其他值

yrdbyhpb  于 2021-07-24  发布在  Java
关注(0)|答案(3)|浏览(267)

我试图找到所有纯粹来自/与某个组织的内部沟通,而不是与任何其他组织的内部沟通。

CREATE TABLE emails
(
     Id INT,
     from_org VARCHAR(50)
);

CREATE TABLE users
(
     emailId INT,
     FullName VARCHAR(50) NOT NULL,
     Organisation VARCHAR(50) NOT NULL
);

INSERT INTO emails (Id, from_org) 
VALUES (1, 'ABC Pty Ltd'), (2, 'ABC Pty Ltd'), 
       (3, 'Point Pty Ltd'), (4,'ABC Pty Ltd');

INSERT INTO users (emailId, FullName, Organisation) 
VALUES (1, 'John Smith', 'ABC Pty Ltd'), 
       (1, 'Maria Jones', 'Point Pty Ltd'), 
       (2, 'Ben Coria', 'ABC Pty Ltd'),
       (3, 'Leo Sen', 'Point Pty Ltd'), 
       (4, 'Leo Sen', 'Point Pty Ltd');

在这个场景中,我想找到所有来自“私人有限公司”(emails.from\ org)的电子邮件,以及发送给内部用户(users.organization='私人有限公司')的电子邮件。我当前的查询是:

SELECT * 
FROM emails 
LEFT JOIN users ON emails.Id = users.emailId 
WHERE emails.from_org = 'ABC Pty Ltd' 
  AND users.organisation = 'ABC Pty ltd'

输出:

| Id  | from_org    | emailId | FullName   | Organisation |
+-----+-------------+---------+------------+--------------+
| 1   | ABC Pty Ltd | 1       | John Smith | ABC Pty Ltd  |
| 2   | ABC Pty Ltd | 2       | Ben Coria  | ABC Pty Ltd  |

预期结果:

| Id  | from_org    | emailId | FullName   | Organisation |
+-----+-------------+---------+------------+--------------+
| 2   | ABC Pty Ltd | 2       | Ben Coria  | ABC Pty Ltd  |

返回的值是因为emailid“2”来自组织“ pty ltd”,只发送给“ pty ltd”,没有其他组织。
sqlfiddle目前不适合我。

42fyovps

42fyovps1#

你可以用 NOT EXISTS 具体如下:

SELECT * 
FROM emails 
LEFT JOIN users u ON emails.Id = u.emailId 
WHERE emails.from_org = 'ABC Pty Ltd' 
  AND u.organisation = 'ABC Pty ltd'
  AND NOT EXISTS (select 1 from users uu 
                   where uu.emailId = u.emailId and u.organisation <> 'ABC Pty ltd')
kgqe7b3p

kgqe7b3p2#

我认为不存在的逻辑在这里工作得很好:

SELECT e.Id, e.from_org, u1.emailId, u1.FullName, u1.Organisation
FROM emails e
INNER JOIN users u1 ON u1.emailId = e.Id
WHERE NOT EXISTS (SELECT 1 FROM users u2
                  WHERE u2.emailId = e.Id AND u2.Organisation <> e.from_org);

演示

简单地说,上面的查询表示返回我们在与该电子邮件相关联的发起组织之外找不到用户的任何电子邮件。

kzmpq1sx

kzmpq1sx3#

这里是另一种方法,您可以首先计算属于单个组织的emailid,并将其与emails表连接起来。

select * from emails e1 join (
select * from users u1 where not exists ( select 1 from users u2 where u1.emailId = u2.emailID and u1.Organisation <> u2.Organisation)) u3
on  e1.Id = u3.emailId and e1.from_org = 'ABC Pty Ltd' and Organisation = 'ABC Pty Ltd'

相关问题