Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2015

    Unanswered: need help with subquery

    i need some help with mysql, i am doing a course , my question is
    for each event, list the prize money available for first, second and third prize on one line. order by event_id. the first line of the table is like
    event_id first second third
    101 120 60 30

    my tables are as follows:
    HORSE (horse_id, name, colour, sire, dam, born, died, gender) SHOWSITE (show_id, name, address)
    JUDGE (judge_id, name, suburb)
    EVENT (event_id, show_id, event_name, judge_id)
    ENTRY (event_id, horse_id, place)
    PRIZE (event_id, place, money)

    i wrote;

    use horsedb;
    select event_id,
    max(first) as first,
    max(second) as second,
    max(third) as third
    from (select event_id,
    case when place = 1 then money else null end as first,
    case when place = 2 then money else null end as second,
    case when place = 3 then money else null end as third
    from prize
    group by event_id;

    the teachers return comment says:
    Have another think about this. How are you matching the event_id and the places within that same event_id? It just so happens that they are in numerical order on event_id and place in the sample data, but if were to mix up the rows in the table your answer won't work. You need to write a solution that will work no matter what the order of data is.

    any help would be appreciated.

  2. #2
    Join Date
    Dec 2007
    Richmond, VA
    Provided Answers: 5
    has your teacher explained to you about keys of tables? And how to properly join tables?

Posting Permissions

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