CODEWITHSUNDEEP
sql
LuxC
LuxC

Reputation: 147

SQL return where IN list and not any other value

I'm trying to find all internal communications that is purely from/to an organisation and not to any other organisation.

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');

In this scenario, I want to find all emails that are from 'ABC Pty Ltd' (emails.from_org) and where it's to an internal user (users.Organisation = 'ABC Pty Ltd'). My current query is:

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

Output:

| 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  |

Expected result:

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

The returned value is because emailId '2' is from_org 'ABC Pty Ltd' and only to 'ABC Pty Ltd' and no other organisation.

sqlfiddle doesn't work for me at the moment.

Upvotes: 1

Views: 542

Answers (3)

kites
kites

Reputation: 1405

Here is an another approach, you can first calculate the emailID's that belong to single organization and join it with emails table.

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'

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521674

I think not exists logic works nicely here:

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);

Demo

Read simply, the above query says to return any email for which we cannot find a user outside of the originating organization which is associated with that email.

Upvotes: 1

Popeye
Popeye

Reputation: 35910

You can use NOT EXISTS as follows:

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')

Upvotes: 3

Related Questions