MySQL Sub-Queries- show results on one row

Question

Question is as follows: For each event, list the prize money available for first, second and third prize on one line.

So I need to list the EVENT_ID from the PRIZE table. Then there will be three separate columns displaying the first, second, and third prize.

Expected data is listed below:

| event_id | First | Second | Third |    
|    101   |  120  |   60   |   30  |     
|    102   |  10   |   5    |   2   |     
|    103   |  100  |   60   |   40  |     
|    401   |  1000 |   500  |  250  |

The table PRIZE has the information as listed:

| Event_id      | Place      | Money      |    
| 101           | 1          | 120        |    
| 101           | 2          | 60         |    
| 101           | 3          | 30         |        
| 102           | 1          | 10         |    
| 102           | 2          | 5          |    
| 102           | 3          | 2          |    
| 103           | 1          | 100        |    
| 103           | 2          | 60         |   
| 103           | 3          | 40         |    
| 401           | 1          | 1000       |    
| 401           | 2          | 500        |    
| 401           | 3          | 250        |   
| 401           | 4          | 100        |    
| 401           | 5          | 50         |

So far I have:

SELECT event_id, money AS First, money AS Second, money AS Third

FROM PRIZE 

WHERE money IN

(SELECT money FROM PRIZE WHERE place=1 IN

(SELECT money FROM PRIZE WHERE place=2 IN

(SELECT money FROM PRIZE WHERE place=3

GROUP BY event_id)));

SQL just gives an 'OK' statement and doesn't show a table. Help please?

Possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) — Strawberry, Nov 30 '15 at 13:37

score 2 · Answer 1 · answered Nov 30 '15 at 13:40

I think the easiest way is to put them all in one column, using substring_index() and group_concat():

select event_id,
       substring_index(group_concat(money order by place), ',', 3)
from prize
group by event_id;

Alternatively, for three separate columns, conditional aggregation is also pretty easy:

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

Thank you, this does work however this is a question from a course I'm completing and I know the teacher will mark this one as incorrect as we haven't learnt quite the above yet (silly i know) — Ashlea, Nov 30 '15 at 13:55

Thomas Landauer · Accepted Answer · 2015-11-30T13:56:21.173

0

My suggestion:

SELECT event_id,
(SELECT Money FROM prize WHERE event_id=p.event_id AND place=1) AS First,
(SELECT Money FROM prize WHERE event_id=p.event_id AND place=2) AS Second,
(SELECT Money FROM prize WHERE event_id=p.event_id AND place=3) AS Third,
FROM prize p

EDIT after comment: Add this:

GROUP BY event_id

edited Nov 30 '15 at 13:56

answered Nov 30 '15 at 13:39

Thomas Landauer

7,857
10
47
99

2

For the next time: Please don't ask homework question with ridiculous restrictions! Gordon Linoff spent some time dealing with *your* problem (not his) and you can't accept it?! See http://meta.stackoverflow.com/questions/266979/contrived-homework-questions – Thomas Landauer Nov 30 '15 at 14:03
I appreciate Gordon's time and effort in answering my question, ultimately not my fault if there is restrictions and I tried my best before coming to an online forum for help. I do agree with the above article but as I've only started coding within the last few weeks, i certainly have a lot to learn. – Ashlea Nov 30 '15 at 14:10

MySQL Sub-Queries- show results on one row

2 Answers2