Geek Answers Handbook

Nested queries in SQL

The purpose of my request is to return the name of the country and its head of state if headofstate has a name starting with A, and in the capital of the country more than 100,000 people using the sub-request.

Here is my request:

SELECT country.name as country, (SELECT country.headofstate from country where country.headofstate like 'A%') from country, city where city.population > 100000;

I tried changing it by putting in a where clause, etc. I do not receive subqueries. I just return errors, for example, "subquery returns more than one row", etc. If someone can help me on how to order it and explain why this should be a certain way, that would be great.

+8
sql nested
source share
4 answers

If it should be "nested", this will be one way to do your job:

 SELECT o.name AS country, o.headofstate FROM country o WHERE o.headofstate like 'A%' AND ( SELECT i.population FROM city i WHERE i.id = o.capital ) > 100000

A JOIN will be more efficient than a correlated subquery. Is the one who ever gave you this task unable to speed himself up?

+15
source share

You need to join two tables and then filter the result in where :

 SELECT country.name as country, country.headofstate from country inner join city on city.id = country.capital where city.population > 100000 and country.headofstate like 'A%'
+7
source share

The request below should help you achieve what you want.

 select scountry, headofstate from data where data.scountry like 'a%'and ttlppl>=100000
+1
source share

As I can see, the only place for a subquery will be in the WHERE clause, for example,

 SELECT country.name, country.headofstate FROM country WHERE country.headofstate LIKE 'A%' AND country.id in (SELECT country_id FROM city WHERE population > 100000)

Also, I have to agree with Adrian: why the hell should you use nested queries?

0
source share

More articles:


All Articles