SQL SELECT using in() but excluding others

Question

I have a table called 'countries' linked to another table 'networks' with a many to many relationship:

  countries             countries_networks                networks
+-------------+----------+  +-------------+----------+  +-------------+---------------+
| Field       | Type     |  | Field       | Type     |  | Field       | Type          |
+-------------+----------+  +-------------+----------+  +-------------+---------------+  
| id          | int(11)  |  | id          | int(11)  |  | id          | int(11)       |
| countryName | char(35) |  | country_id  | int(11)  |  | name        | varchar(100)  |
+-------------+----------+  | network_id  | int(11)  |  | description | varchar(255)  |

To retrieve all countries that have a network_id of 6 & 7, I just do the following: ( I could go further to use the networks.name but I know the countries_networks.network_id so i just use those to reduce SQL.)

SELECT DISTINCT countryName 
 FROM countries AS Country
INNER JOIN countries_networks AS n ON Country.id = n.country_id
 WHERE n.network_id IN (6,7)

This is fine, but I then want to retrieve the countries with a network_id of JUST 8, and no others.

I'ver tried the following but its still returning networks with 6 & 7 in. Is it something to do with my JOIN?

SELECT DISTINCT countryName 
 FROM countries AS Country
INNER JOIN countries_networks AS n ON Country.id = n.country_id
 WHERE n.network_id IN (8)
AND n.network_id not IN(6,7)

Thanks.

Answer 1

You need two joins:

SELECT  DISTINCT c.CountryName
FROM    Countries c
        INNER JOIN
                countries_networks n
                ON c.id = n.country_id
                AND n.network_id = 8
        LEFT JOIN
                countries_networks n2
                ON c.id = n2.country_id
                AND n2.network_id IN (6, 7)
WHERE   n2.country_id IS NULL

As things stand in your query, all you are checking as your last line is that 8 is not in the list (6, 7). If I read your question right, you want countries that do have a network with ID 8, but that don't have a network with ID 6 or 7. Each needs its own join, and you want to make sure that there are no matching rows for the second.

Answer 2

One more solution using NOT EXISTS predicate.

SELECT DISTINCT countryName 
FROM countries AS Country
INNER JOIN countries_networks AS n ON Country.id = n.country_id
WHERE n.network_id IN (8)
AND NOT EXISTS (SELECT 1 FROM countries_networks n1 
   WHERE n1.country_id = Country.id AND n1.network_id !=8)