SQL- Fetching values with multiple values in 1 column

Question

Hey I've been learning SQL and got stuck on one question. Let's say the database looks like this:

I want to retrieve all of the countries that have a poor AND average rating. In this case, it will only return Brazil and Chile.

Country	Rating
Brazil	Good
Brazil	Average
Brazil	Poor
Chile	Poor
Chile	Average
Chile	Good
Argentina	Average
Peru	Poor
Peru	Outstanding

I've tried were conditions, IN conditions but it just returns me everything that contains either poor or average.

thanks!

Answer 1

IIUC, you are looking for

SELECT Country FROM your_table
WHERE Rating in ('Poor','Average')
GROUP BY Country 
HAVING COUNT(DISTINCT Rating)=2