SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast alexkmoore's Avatar
    Join Date
    Jan 2010
    Location
    North Bay, ON
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Nested Select Query Syntax Trouble

    On this page http://sqlzoo.net/1a.htm there is a question that asks me to show the names of all the countries in Europe that have a GDP per capita that is greater than the UK.

    I have so far:
    Here's how to list the countries in Europe
    select name from bbc where region = 'europe'

    Here's how to show the GDP per captia of the UK
    select name, gdp/population from bbc where name = 'united kingdom'

    So using these two statements, I tried nesting them together like this:
    select name from bbc where gdp/population IN (select name, gdp/population from bbc where region = 'europe') >
    (select name, gdp/population from bbc where name = 'united kingdom')


    and the error message I got says: sql: errorOperand should contain 1 column(s)
    number: 1241


    I can't seem to properly nest these select queries together and I believe it's due to an error in my syntax. Can someone show me how to put this together?

    Thanks

    Alex

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    let me explain the error first, before making suggestions to fix it

    when you use this construction...
    Code:
    [WHERE] expression IN ( subquery )
    you must be sure that the subquery returns only one column, so that the expression, which is a single value, can be compared to these individual column values, looking for a match

    your subquery returns two columns

    try this instead --
    Code:
    SELECT name 
      FROM bbc 
     WHERE region = 'europe'
       AND gdp/population = 
           ( SELECT gdp/population 
               FROM bbc 
              WHERE name = 'united kingdom' )
    note the use of AND in the WHERE clause, to combine two conditions

    you should also learn to use formatting in your code, with line breaks and indentation -- you will thank me later
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast alexkmoore's Avatar
    Join Date
    Jan 2010
    Location
    North Bay, ON
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I just tried that out and it works perfectly! Thanks for helping me with the syntax and formatting!

    Alex


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •