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

SELECT  countryName
FROM    countries
WHERE   country_id IN
        (
        SELECT  country_id
        FROM    network
        WHERE   network_id = 8
        )
        AND country_id NOT IN
        (
        SELECT  country_id
        FROM    network
        WHERE   network_id IN (6, 7)
        )

Answer 3

You could do a subquery:

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

Although this may not be optimal.

Answer 4

The reason that you get countries that has a network id of 6 and 7 is that you are checking the condition on each countries_networks record. The part of the condition that excludes the 6 and 7 has no effect at all, as any id that is 8 never can be 6 or 7 at the same time.

You need to join in the countries_networks table twice, so that you can use one to include countries and one to exclude countries:

select countryName
from countries c
inner join countries_networks i on i.country_id = c.id and i.network_id = 8
left join countries_networks e on e.country_id = c.id and e.network_id in (6,7)
where e.id is null

Also, you don't need to make this query distinct, unless you actually have multiple connections between the same country and network id.

Answer 5

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)

Answer 6

Although you're resisting an additional join on the networks table, it really is the simplest way. If you test your id against that table, your result should be limited appropriately:

SELECT countryName 
  FROM countries AS Country JOIN countries_networks AS cn 
                              ON Country.id = cn.country_id
                            JOIN networks n
                              ON cn.network_id = n.id
 WHERE n.network_id IN (8)

Testing against the networks table should limit results without regard to anything that may be linked in the countries_networks table. It should also perform (and read) better than subqueries or any other solution I can think of.