Reputation: 399
After making this sql request:
SELECT user.id, campaign_id, user_id
FROM `user`
LEFT OUTER JOIN `email_solus`
ON email_solus.user_id=user.id
I have this table.
id campaign_id user_id
------ ----------- ---------
1 3 1
1 5 1
1 6 1
2 (NULL) (NULL)
3 (NULL) (NULL)
4 (NULL) (NULL)
5 (NULL) (NULL)
7 (NULL) (NULL)
8 (NULL) (NULL)
9 (NULL) (NULL)
10 (NULL) (NULL)
12 (NULL) (NULL)
13 (NULL) (NULL)
14 (NULL) (NULL)
15 (NULL) (NULL)
16 (NULL) (NULL)
17 (NULL) (NULL)
What I want to make is unselect all elements with campaign_id=3, with all the common ids as well. In this case I'd need to remove all elements with id=1, having a table like this:
id campaign_id user_id
------ ----------- ---------
2 (NULL) (NULL)
3 (NULL) (NULL)
4 (NULL) (NULL)
5 (NULL) (NULL)
7 (NULL) (NULL)
8 (NULL) (NULL)
9 (NULL) (NULL)
10 (NULL) (NULL)
12 (NULL) (NULL)
13 (NULL) (NULL)
14 (NULL) (NULL)
15 (NULL) (NULL)
16 (NULL) (NULL)
17 (NULL) (NULL)
Upvotes: 1
Views: 74
Reputation: 727137
You can use NOT EXISTS
to do the filtering, like this:
SELECT user.id, campaign_id, user_id
FROM `user`
LEFT OUTER JOIN `email_solus` ON email_solus.user_id=user.id
WHERE NOT EXISTS (
SELECT * FROM `email_solus` es WHERE es.user_id=user_id AND es.campaign_id=3
)
Upvotes: 2
Reputation: 2083
SELECT user.id, campaign_id, user_id
FROM `user`
LEFT OUTER JOIN `email_solus`
ON email_solus.user_id=user.id
where user.id not in
(select user.id from 'user'
where campaign_id = 3)
should work, although 'not in' can be inefficient if you've got a large dataset
Upvotes: 0
Reputation: 839264
SELECT user.id, campaign_id, user_id
FROM user
LEFT JOIN email_solus
ON email_solus.user_id=user.id
WHERE user.id NOT IN
(
SELECT user_id
FROM email_solus
WHERE campaign_id = 3
)
Upvotes: 3