Multiple IN() operators - No results

I have a query:

SELECT DISTINCT
countryName,countrySlug
FROM countries AS Country 
INNER JOIN countries_networks AS n ON Country.id = n.country_id
AND n.network_id IN (1,14)

which works fine. However, I now need to add a 'must have' clause to it, so that the n.network_id MUST also be in the set (6,7,8,9). (BTW they can have multiple network_ids, as I'm pulling directly from a lookup table.)

So I tried adding another IN():

SELECT DISTINCT
    countryName,countrySlug
    FROM countries AS Country 
    INNER JOIN countries_networks AS n ON Country.id = n.country_id
    AND n.network_id IN (1,开发者_如何学JAVA14)
AND n.network_id IN (6,7,8,9)

which now returns no results at all.

This seems like I'm making a stupid error here. Can anyone see what it is?

Yes; you're requiring that network_id be present in two non-intersecting lists. It is impossible for a given value a to be in both of the following lists:

1, 14
6, 7, 8, 9

To beat a dead horse, let's look at every value

value  list1 list2
------------------
1      x
6            x
7            x
8            x
9            x
14     x

That's the entire set of values that falls in either list; anything outside of that scope won't fit either condition, and none of the values within the scope fit both conditions.

To satisfy your condition where a single Country can have multiple network_ids based on the country_network association table, you can do this:

select distinct
    c.countryname, c.countryslug

from country c

join country_network cn1 on cn1.country_id = c.country_id
join country_network cn2 on cn2.country_id = c.country_id

where cn1.network_id in (1, 14) and cn2.network_id in (6, 7, 8, 9)

Yes.

A given network_id row cannot simultaneously be in both sets. (1,14) and (6,7,8,9)

You can achieve your desired result with a self join.

... countries_networks cn1 
   join countries_networks cn2 on cn1.country_id = cn2.country_id
where cn1.network_id in (1,14) and cn2.network_id in (6,7,8,9)

Based on my comment above, try the following:

SELECT DISTINCT countryName,countrySlug
FROM countries AS Country 
WHERE Country.id IN (SELECT n.country_id FROM countries_networks n WHERE n.network_id IN (1,14))
AND Country.id IN (SELECT n.country_id FROM countries_networks n WHERE n.network_id IN (6,7,8,9))

Performance wise, there may be a hit from the two sub-queries but it may be inconsequential depending on the number of rows in your tables. I'll have a go at using joins and update...

UPDATE

Using a double join, this should yield the results you need:

SELECT DISTINCT countryName,countrySlug
FROM countries AS Country
INNER JOIN countries_networks n1 ON n1.country_id = Country.id
INNER JOIN countries_networks n2 ON n2.country_id = Country.id
WHERE n1.network_id IN (1,14)
AND n2.network_id IN (6,7,8,9)

IN () is a synonym for a series of OR's

So WHERE a IN (1,2,4)
can also be written as

WHERE (a = 1 OR a = 2 OR a = 4)  

You are trying to do:

WHERE ... a IN (1,14) AND a IN (6,7,8,9)

Which can be rewritten as

WHERE (a = 1 OR a = 14) AND (n.network_id = 6 OR ....)

Never mind that you're doing it in a join clause and never mind that I did not write out the full equivalent code. Clearly network_id cannot be two different values at the same time, which is why your query never returns anything.