SQL SELECT using in () but excluding others

Question

I have a table called "countries" related to networks of other tables with many, many relationships:

  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 get all countries that have network_id of 6 and 7, I just do the following: (I could go further to use network.name, but I know country_networks.network_id, so I just use them to shorten 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 normal, but then I want to get countries with network_id from JUST 8 and others.

I tried the following, but its still returning networks with 6 and 7 inches. Is this something related to 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)

Thank.

Answer 1

You need two connections:

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

, , , 8 (6, 7). , , , ID 8, 6 7. , , .

Answer 2

, NOT EXISTS.

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 3

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 4

:

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))

.

Answer 5

, 6 7, , countries_networks. , 6 7, , , 8, 6 7 .

countries_networks, :

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

, , .

Answer 6

networks, . , :

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)

networks , countries_networks. ( ) , , .