CODEWITHSUNDEEP
sqlnested
ZAX
ZAX

Reputation: 1008

Nesting queries in SQL

The goal of my query is to return the country name and its head of state if it's headofstate has a name starting with A, and the capital of the country has greater than 100,000 people utilizing a nested query.

Here is my query:

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

I've tried reversing it, placing it in the where clause etc. I don't get nested queries. I'm just getting errors back, like "subquery returns more than one row" and such. If someone could help me out with how to order it, and explain why it needs to be a certain way, that'd be great.

Upvotes: 13

Views: 121576

Answers (4)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656754

If it has to be "nested", this would be one way to get your job done:

SELECT o.name AS country, o.headofstate 
FROM   country o
WHERE  o.headofstate like 'A%'
AND   (  -- guaranteed to return a single value
    SELECT i.population
    FROM   city i
    WHERE  i.id = o.capital
    ) > 100000;

A "nested query" is an odd requirement for the task. A JOIN is simpler and more efficient:

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

Upvotes: 17

user3585283
user3585283

Reputation: 21

Query below should help you achieve what you want.

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

Upvotes: 2

sqrtsben
sqrtsben

Reputation: 256

The way I see it, the only place for a nested query would be in the WHERE clause, so e.g.

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

Apart from that, I have to agree with Adrian on: why the heck should you use nested queries?

Upvotes: 2

Adriano Carneiro
Adriano Carneiro

Reputation: 58615

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

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%'

Upvotes: 9

Related Questions