-2

One of the questions in my homework in databases:

"In which countries do you speak Swedish but not Finnish? The answer should consist of countrynames."

This is my piece of code:

Select Name from Country

left outer join CountryLanguage

on CountryLanguage.Countrycode = Country.Code
 
where (CountryLanguage.Language = 'Swedish') and (CountryLanguage.Language not in ('Finnish'))

I don't know how to actually use the function "NOT IN". As output I would like to get Denmark, Normay and Sweden. Finland should not be in the output since they do speak Swedish in Finland but also Finnish, and the question was "where they ONLY speak Finnish".

Shadow
  • 33,525
  • 10
  • 51
  • 64
Odin Röblom
  • 3
  • 1
  • I know that this question is pretty straightforward on its face, but the purpose of a question here is to create an answer that contributes to a library of solutions that are useful to many users. It would help to add more concrete information, like example data and information about the schema you're using. As written, this requires the reader to infer the schema by common sense. Also, if you're interested in using `NOT IN` to solve this, consider the discussion in https://stackoverflow.com/questions/5231712/sql-not-in-not-working – WaitingForGuacamole Feb 23 '21 at 14:44

1 Answers1

1

One method uses aggregation. For these two particular languages, you can use:

Select c.Name
from Country c left outer join
     CountryLanguage cl
     on cl.Countrycode = c.Code
where cl.Language in ('Swedish', 'Finnish')
group by c.Name
having min(cl.Language) = 'Swedish';
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786