Results 1 to 5 of 5

Thread: subquery

  1. #1
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58

    Unanswered: subquery

    I'm writing my first subquery within an outer query and can't work out what I'm doing wrong:

    from the entry table (event_id, horse_id, place)
    and the horse table (horse_id, horse_name)
    I have to list the horse_id and horse_name for each horse that has been placed in the top 3 (ie, got place 1, 2 or 3) two or more times.

    this is my subquery, which worked:

    select H.horse_id, H.horse_name
    from entry E, Horse H
    where E.horse_id = H.horse_id
    and E.place < 4;

    this is the subquery within the outer query, which didn't work:

    select H.horse_id, H.horse_name
    from horse H
    where 2 <= (select count(*)
    from entry E, horse H
    where E.horse_id = H.horse_id
    and E.place < 4);

    Can anyone spot my error, please?

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Try this:

    Code:
    SELECT h.horse_id,
           h.horse_name
    FROM   horse h,
           entry e
    WHERE  e.horse_id = h.horse_id
           AND e.place <= 3
    GROUP  BY h.horse_id,
              h.horse_name
    HAVING COUNT(1) > 1;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by melgra70 View Post
    Can anyone spot my error, please?
    • The subquery you have doesn't join to the horse table outside the brackets but to the horse table within the brackets.
    • As it-iss said you also don't need a subquery.
    • His query is far more efficient than yours (and easier to read).
    • Why is the '4' in quotes?

  4. #4
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58

    subquery

    Thanks, Ronan and Mike.
    Your solution is great and works well, but this exercise requires that I show competency in subquery creation. Hence, I still need to manipulate my solution with the subquery added into an outer query. I've removed the '4'.

    select H.horse_id, H.horse_name
    from entry E, Horse H
    where E.horse_id = H.horse_id
    and E.place <=3;

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by melgra70 View Post
    this exercise requires that I show competency in subquery creation
    I suggest you read the comments I made regards your code and fix it.
    Alternatively you could just pay someone to do your homework for you.

Posting Permissions

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