Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58

    Unanswered: Query across columns

    I have an entry table and horse table.
    The following result was achieved from a query on these two tables:

    SELECT Entry.Horse_ID, Horse.Horse_Name, Entry.Place
    FROM Horse INNER JOIN Entry ON Horse.Horse_ID = Entry.Horse_ID

    Horse_ID Horse_Name Place
    101 Flash 1
    101 Flash 1
    102 Star 2
    102 Star 7
    201 Boxer 3
    201 Boxer 2
    201 Boxer 3
    301 Daisy 4
    301 Daisy 2

    I need to present this data in the following format:

    Horse_ID Horse_Name Best Place Worst Place

    Can anyone head me in the right direction, please?

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    You will need to use MIN(place) for best place and MAX(place) for worst place, providing this has been registered. If there is a NULL entry for place then you will have to decide what you will need to do with this and how this should be interpreted.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    May 2008
    Posts
    7
    i think there is need of using Pivot, which can display the result of rows as columns. you can search on that.

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    try something like:

    Code:
    SELECT Entry.Horse_ID
           , Horse.Horse_Name
           , min(min_place.Entry.Place)
           , max(max_place.Entry.Place)
        FROM Horse
    INNER JOIN Entry as min_place
         ON Horse.Horse_ID = Entry.Horse_ID
    INNER JOIN Entry as max_place
         ON Horse.Horse_ID = Entry.Horse_ID
    group by  Entry.Horse_ID
           , Horse.Horse_Name
    Dave

  5. #5
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58
    Thanks, Dave.
    I can see where this is going and answers a question I was just going to ask, so thank you.

    I've run the query and there is a Syntax error (missing operator) in query expression
    'Horse.Horse_ID = Entry.Horse_ID
    INNER JOIN Entry as max_place
    ON Horse.Horse_ID = Entry.Horse_ID'

    I'm not sure what the missing operator could be.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
       FROM Horse
    INNER JOIN Entry as min_place
         ON Horse.Horse_ID = min_place.Horse_ID
    INNER JOIN Entry as max_place
         ON Horse.Horse_ID = max_place.Horse_ID
    make sense?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58
    Yes, Rudy - that's very clear, thank you.
    This now takes the query to:

    SELECT Entry.Horse_ID
    , Horse.Horse_Name
    , min(min_place.Entry.Place)
    , max(max_place.Entry.Place)
    FROM Horse
    INNER JOIN Entry as min_place
    ON Horse.Horse_ID = min_place.Horse_ID
    INNER JOIN Entry as max_place
    ON Horse.Horse_ID = max_place.Horse_ID
    group by Entry.Horse_ID
    , Entry.Horse_Name;

    However, Entry.Horse_ID was coming up as an "unknown field", so I changed that one to Horse.Horse_ID.
    That was accepted but then the field min_place.Entry.Place caused another "unknown field" error.
    So I changed that to min_place.Place (ditto for max_place.Entry.Place)
    Then, finally I got an "unknown field" on the final Entry.Horse_ID field and so I changed that to Horse.Horse_ID and Bingo! it works!

    Thanks so much, Rudi.
    It was good to see your working and to be able to understand it.

    Regards,
    Melgra.

Posting Permissions

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