Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2012
    Posts
    5

    Unanswered: comparing more than one row

    Hallo
    I'm trying to run this query
    SELECT * FROM pop_age_sex_industry WHERE Country_area=(select * from countries);

    and countries ist as view

    CREATE VIEW countries(country) as(
    SELECT distinct Country_area FROM pop_age_sex_industry
    )

    and im getting the error: more than one row returned by a subquery used as an expression

    I know it is because im trying to compare Country_area with countries but how i can write it so i can do this query for all the rows of countries??

    Thanks soo much for every tip

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    You have two errors in your query:

    1) The subselect should only return a single [b]column[b] (not all columns using *)
    2) When using = then the sub-select may only return a single row (as the error message indicates).

    You probably want:

    Code:
    SELECT *
    FROM pop_age_sex_industry
    WHERE country_area IN (SELECT country FROM countries)
    Note the usage of IN instead of =

    But to be honest: you query does not make any sense to me. The result of the above will be all rows from the pop_age_sex_industry table. The view contains all values for the country_area column from that table and thus you query will in turn return everything.

    What is it you are really trying to achieve?

    In the future please format you SQL statements using [code] tag.
    Details are in the forum help: http://www.dbforums.com/misc.php?do=bbcode

  3. #3
    Join Date
    Jun 2012
    Posts
    5
    Quote Originally Posted by shammat View Post
    You have two errors in your query:

    1) The subselect should only return a single [b]column[b] (not all columns using *)
    2) When using = then the sub-select may only return a single row (as the error message indicates).

    You probably want:

    Code:
    SELECT *
    FROM pop_age_sex_industry
    WHERE country_area IN (SELECT country FROM countries)
    Note the usage of IN instead of =

    But to be honest: you query does not make any sense to me. The result of the above will be all rows from the pop_age_sex_industry table. The view contains all values for the country_area column from that table and thus you query will in turn return everything.

    What is it you are really trying to achieve?

    In the future please format you SQL statements using [code] tag.
    Details are in the forum help: http://www.dbforums.com/misc.php?do=bbcode
    I'm sry cause of the unformatted statements ,this was my first post
    well I have this Task
    Find the top five industries regarding number of employees per country and year
    and the table looks like this
    Code:
    create table Pop_age_sex_industry(Country_Area varchar,Year varchar,Arvarchar,Sexvarchar,Age text,
    Industry varchar,Record_type varchar,Reliability varchar,Source_year varchar,
    	value numeric,value_footnotes text)
    Do you have any tipp how can I start with it?? i was trying many things this was why i the query of my first post looked so bad ,i was just trying to conquer the query into many pieces but I dont know how start
    Thxxxx

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... varchar,Arvarchar,Sexvarchar,Age ...
    What are these things?

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    CREATE VIEW countries(country) AS
    SELECT distinct Country_area
       FROM pop_age_sex_industry
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jun 2012
    Posts
    5
    OO I'm sorry i've just copied it ,but something hast got wrong ^^, it looks like this
    Code:
    create table Pop_age_sex_industry(Country_Area varchar,Year varchar,Area varchar,Sex varchar,Age text,
    Industry varchar,Record_type varchar,Reliability varchar,Source_year varchar,
    	value numeric,value_footnotes text)

Posting Permissions

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