CODEWITHSUNDEEP
mysql
malteser
malteser

Reputation: 485

Showing Countries which do not form part of the query - mysql

So I have this probelm, Where I have to display the names or the countries that do not form part of the organization 'WHO' in the table ismember. This is the SQL I came up with

SELECT DISTINCT ismember.country, country.name
FROM ismember LEFT JOIN country ON ismember.country = country.code
WHERE ismember.organization NOT IN (
    SELECT DISTINCT ismember.organization
    FROM ismember
    WHERE ismember.organization = 'WHO'
)
GROUP BY organization 
HAVING COUNT(organization) > 1 ;

It works, and it does return the countries who are not in 'WHO'. My problem is that this method excludes the countries which are part of no organizations. I.E it excludes those countries that do not have a entry in the ismember table.

Any Ideas how This could be solved

Upvotes: 0

Views: 608

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 95033

This is no answer but an explanation what your query does:

  1. The subquery

    SELECT DISTINCT ismember.organization FROM ismember WHERE ismember.organization = 'WHO'

This gets the string 'WHO' for every record in the table where organization is 'WHO' and then reduces all the 'WHO' to one 'WHO'.

  1. NOT IN

Accordingly WHERE ismember.organization NOT IN (subquery) is the same as WHERE ismember.organization <> 'WHO'

  1. LEFT JOIN

You outer left join the table country to ismember. That means in case ismember contains a country which is not in the country table, you still get that record. But why would a country not be in the country table? That doesn't seem to make sense. You might get a record like 123, NULL meaning that country code 123 is no valid country, but what the heck?

  1. GROUP BY

You group by organization. So you get one result row per organization. Didn't you say you want countries listed?

  1. Aggregation

Then you select ismember.country and country.name per organization. As you don't specify any desired aggregation like MAX(ismember.country) or MIN(country.name), you get a random match. So if there is an organization 'ABC' with the countries 1, 2 and 3, you get one of the three randomly.

  1. HAVING

HAVING COUNT(organization) is about the same as HAVING COUNT(*), i.e. the count of countries per organization. To be precise: It is the same for all organizations that are not null (for null you'd get 0 with the first expression and the country count for the second). You want that country count for the organization to be greater than 1. So you remove organizations with only one member.

  1. DISTINCT

If the random country code for 'ABC' is 1 and the random country name is 'Italy' (they don't have to belong together, both are just independent random picks of the organization) and the same pair happens to be the pick for an organization 'DEF', then you'd remove one of them so you get 1, 'Italy' just once.

  1. Surprise

I am surprised you say this query works with a minor flaw. Sorry, I find this hard to believe.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270181

One method to approach this is simply with aggregation and a having clause:

SELECT im.country, c.name
FROM ismember im LEFT JOIN
     country c
     ON im.country = c.code
GROUP BY m.country, c.name
HAVING SUM(im.organization = 'WHO') = 0;

Upvotes: 0

Joel Hinz
Joel Hinz

Reputation: 25404

Why not just something like this?

SELECT code, name FROM country WHERE code NOT IN (
    SELECT country FROM ismember WHERE organization = 'WHO'
)

Should do the same thing. :)

Upvotes: 1

Related Questions