CODEWITHSUNDEEP
mysqlsql
Niclas
Niclas

Reputation: 1406

SELECT WHERE IN() AND NOT IN()

I have three tables

Articles        Channels        ArticlesChannels
pk    id        pk    id        pk    articlePk    channelPk
1     a1        1     c1        1     1            1
2     a2        2     c2        2     1            2
                                3     2            2

Now I want to select all Articles that are included in one collection AND excluded in another.

Ex: Select all articles that have Channel a1 AND not Channel a2.
Correct result: Empty since both articles are in Channel c2.

Ex: Select all articles that have Channel a1.
Correct result: Article1 only one in Channel c1.

My SQL looks like this:

SELECT a.id 
FROM articles a, channels c, articlechannels ac 
WHERE a.pk=ac.articlePk 
AND ac.channelPk = c.pk 
AND c.id IN ('a1') 
AND c.id NOT IN ('a2');

The problem is that the result doesn't exclude result that are existing in the c.id NOT IN ('a2').

Any ideas?

Upvotes: 3

Views: 10634

Answers (1)

user1455836
user1455836

Reputation: 752

Your query looks just fine except for typos.

The problem is it doesn't fit for this requirement:

Ex: Select all articles that have Channel a1 AND not Channel a2. Correct result: Empty since both articles are in Channel c2.

Looks like you want Set1 minus Set2 operation which is not supported by MySQL.

But you can workaround it using something like this:

select a.id, sum(case when c.id = 'c1' then 1 else 0 end) as c1, sum(case when c.id = 'c2' then 1 else 0 end) as c2
from Articles a
inner join ArticleChannels ac
on a.pk = ac.articlePk
inner join Channels c
on c.pk = ac.channelPk
group by a.id
having c1 = 1 and c2 = 0;

Here is SQL Fiddle for the case

UPDATED: You can also go on with subquery:

SELECT a.id 
FROM articles a, channels c, articlechannels ac 
WHERE a.pk=ac.articlePk 
AND ac.channelPk = c.pk 
AND c.id IN ('a1') 
AND a.id NOT IN (
  SELECT a.id 
  FROM articles a, channels c, articlechannels ac 
  WHERE a.pk=ac.articlePk 
  AND ac.channelPk = c.pk 
  AND c.id NOT IN ('a2')
)

Upvotes: 2

Related Questions