Sql Group By Using Strings In New Columns

I have a database of race results. Each race event has multiple classes. events table: event_id | event_date ---------+------------ 1 | 11/5/14 2 | 11/12/14 3

Solution 1:

You can join the events table on two queries from the results table, one for each class:

SELECT    event_data, class_40_winner, class_30_winner
FROM      events e
LEFT JOIN (SELECT result_event, name AS class_40_winner
           FROM   results 
           WHEREclass = 40AND position = 1) c40 ON e.id = c40.result_event
LEFT JOIN (SELECT result_event, name AS class_30_winner
           FROM   results 
           WHEREclass = 30AND position = 1) c30 ON e.id = c30.result_event

Solution 2:

You are querying like pivoting data, so I suggest you to use a query like this:

select event_date
    , max(casewhen r.class = 40then name end) `Class40 Winner`
    , max(casewhen r.class = 30then name end) `Class30 Winner`
from events e
left join results r on e.event_id = r.result_event and r.position = 1groupby event_date;

[SQL Fiddle Demo]