SQL SELECT using in() but excluding others.
- by Pickledegg
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.
