Results 1 to 4 of 4
  1. #1
    Join Date
    May 2013
    Posts
    3

    Unanswered: getting error in a join query

    I have a query here: For each country in "America", give the highest mountain and its height; ordered by height. and tables:

    encompasses : country,continent
    geo_mountain: mountain,country
    mountain:name,height
    i wrote so:


    Code:
    select co.country,mo.name, max(mo.height) as mheight
     from mountain mo
      cross join encompasses co
      join geo_mountain gmo
      on mo.name=gmo.mountain 
      where  gmo.country=any (select country from encompasses where continent='America') and co.continent='America'
    GROUP BY co.country,mo.name
    order by mheight;

    but i get a lot of results, please rewrite my query if possible


    im using oracle 11g, ubintu 12.4
    thank you so much

    best,
    david

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    One of the things you will find, after you move from School to an IT department, is that your questions themselves will be questioned. For example, does the query give you an error, or is it just returning the wrong results? Your title says one thing, and the body implies another.

    As to the query, why do you have the cross join?

  3. #3
    Join Date
    May 2013
    Posts
    3
    Hi thank u
    no error, just a lot of results ,as i said it shouldn't be a lot
    yes i now but when i change " cross join encompasses co" to "join geo_mountain gmo ,encompasses co" i get error and i think it also wont be correct.

    would you please rewrite this query ?
    thank u

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Think about this. What is a "cross join". It looks like you may not be removing the cross join correctly (or "completely" is a better word). What should the syntax look like without the cross join?

Tags for this Thread

Posting Permissions

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