CODEWITHSUNDEEP
mysqldatabase
itaka
itaka

Reputation: 399

SQL. Remove from results some rows depending of one field

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

Answers (3)

Sergey Kalinichenko
Sergey Kalinichenko

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

FJT
FJT

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

Mark Byers
Mark Byers

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

Related Questions