Multiple Nesting SQL Queries ?

[sobias]

Hi guys,

This is my first time using SQL. So, go easy on me :)
I'm executing these queries on some websites online where there is an already made database with Country name,Area,Population,etc

What I would like to do is, to get the names of all the countries in Asia or Africa that starts with 'A' and their population > 40000

I think my nesting is wrong. Isn't it possible to make more than 2 nesting in SQL?

SELECT name, area, population FROM world
   WHERE  name
     IN
        (SELECT name FROM world
         WHERE continent = 'Asia' OR continent = 'Africa' ) 
         IN
            (SELECT name FROM world
             WHERE name LIKE 'A%')
             IN
                 (SELECT name FROM world
                  WHERE population > 50000 )

[sobias]

Answering to my self:

SELECT name, area, population FROM world
   WHERE  continent IN 
       ( 'Asia' , 'Africa')   AND  population > 37100000

So, since all my queries are in one TABLE(world) , then no need to do what I did above. However, the question now is, what if the TABLE(world) does not have the attribute/column population ? and it's in another TABLE called 'Statistics' ? how will I write my query ?

[adam_k]

You keep mentioning SQL, but you are in oracle subforum. I'm going to go with SQL.
You probably should join the 2 tables. You can use in, but that's usually not that good with resources.
To join you'd:

select name, area, population from world 
inner join statistics --could be other type of join, but in your case it's join
on world.fieldname = statistics.fieldname --replace world.fieldname, statistics.fieldname with the column(s) that the 2 tables share and keep adding columns until you get a 1 to 1 relationship 
and world.fieldname2 = statistics.fieldname3 
where continent in ('Asia','Africa') and population > 37100000
and statistics.fieldname4 like 'A%' -- Or whatever. 

To answer your first question, SQL does support multiple nested queries, but your syntax was wrong:

SELECT name, area, population FROM world
   WHERE  name
 IN
    (SELECT name FROM world
     WHERE continent = 'Asia' OR continent = 'Africa' ) 
 and name IN
        (SELECT name FROM world
         WHERE name LIKE 'A%')
 and name IN
             (SELECT name FROM world
              WHERE population > 50000 )

or

SELECT name, area, population FROM world
   WHERE  name
 IN
    (SELECT name FROM world
     WHERE continent = 'Asia' OR continent = 'Africa' 
     and name IN
        (SELECT name FROM world
         WHERE name LIKE 'A%' 
         and name IN
             (SELECT name FROM world
              WHERE population > 50000 ) ))

Please note that I haven't tested the above queries and might contain typos or other errors.

[sobias]

Thank you so much Adam!

Yes, I think I see how SQL syntax strucure is now. I will have to look into join before I test and ask any questions regarding it.

There is something I don't get about IN and = operators - when to use each of them? I think if I did the following(below) I'll get pretty much the same thing right?

SELECT name, area, population FROM world
   WHERE  name
 =
    (SELECT name FROM world
     WHERE continent = 'Asia' OR continent = 'Africa' ) 
 and name =
        (SELECT name FROM world
         WHERE name LIKE 'A%')
 and name IN
             (SELECT name FROM world
              WHERE population > 50000 )

If a moderator watching this post, please kindly move it to the suitable forum.

[pritaeas]

= is used when you compare a column against a single value.

IN is used when you want to compare against a range of values.