Nesting queries in SQL

Question

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  gt  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

User · Answer

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  gt  100000 and country headofstate like  A

User · Answer

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         SELECT i population     FROM   city i     WHERE  i id   o capital        gt  100000   A JOIN would be more efficient than a correlated subquery  though  Can it be  that who ever gave you that task is not up to speed himself

User · Answer

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  gt  100000    Apart from that  I have to agree with Adrian on  why the heck should you use nested queries

User · Answer

Query below should help you achieve what you want   select scountry  headofstate from data  where data scountry like  a  and ttlppl gt  100000

[sql] Nesting queries in SQL

Examples related to sql

Examples related to nested