Harald Fuchs wrote:
> "Sam Russo" <sam@stripped> writes:
>
>>I recieve a delimited file whose fields are:
>>day,slot,subject,room
>>An example of this file is:
>>2,1,Mat,R1
>>3,1,Sci,R6
>>1,2,Sci,R6
>>3,2,Mat,R3
>>1,3,Eng,R2
>>2,3,Eng,R5
>>1,4,Mat,R7
>>3,4,Eng,R9
>
>>I need a mysql query that will generate a timetable which looks like:
>> Day1 Day2 Day3
>>-------------------------------
>>1 Mat R1 Sci R6
>>2 Sci R6 Mat R3
>>3 Eng R2 Eng R5
>>4 Mat R7 Eng R9
>
> If there are only three days, you could use the following:
>
> CREATE TEMPORARY TABLE slots (slot TINYINT UNSIGNED NOT NULL PRIMARY KEY) AS
> SELECT DISTINCT slot FROM tbl;
>
> SELECT s.slot,
> coalesce(concat(t1.subject, ' ', t1.room), '') AS Day1,
> coalesce(concat(t2.subject, ' ', t2.room), '') AS Day2,
> coalesce(concat(t3.subject, ' ', t3.room), '') AS Day3
> FROM slots s
> LEFT JOIN tbl t1 ON t1.slot = s.slot AND t1.day = 1
> LEFT JOIN tbl t2 ON t2.slot = s.slot AND t2.day = 2
> LEFT JOIN tbl t3 ON t3.slot = s.slot AND t3.day = 3;
We could do without the temporary table by simply moving the DISTINCT to the
SELECT, though I expect that's less efficient. We should also probably
explicitly ORDER BY slot. In this case, an index on slot (as you did in the
temporary table) would probably help.
SELECT DISTINCT s.slot,
coalesce(concat(t1.subject, ' ', t1.room), '') AS Day1,
coalesce(concat(t2.subject, ' ', t2.room), '') AS Day2,
coalesce(concat(t3.subject, ' ', t3.room), '') AS Day3
FROM slots s
LEFT JOIN tbl t1 ON t1.slot = s.slot AND t1.day = 1
LEFT JOIN tbl t2 ON t2.slot = s.slot AND t2.day = 2
LEFT JOIN tbl t3 ON t3.slot = s.slot AND t3.day = 3
ORDER BY s.slot;
We could also accomplish both the DISTINCT and the ORDER BY using GROUP BY
instead:
SELECT s.slot,
coalesce(concat(t1.subject, ' ', t1.room), '') AS Day1,
coalesce(concat(t2.subject, ' ', t2.room), '') AS Day2,
coalesce(concat(t3.subject, ' ', t3.room), '') AS Day3
FROM slots s
LEFT JOIN tbl t1 ON t1.slot = s.slot AND t1.day = 1
LEFT JOIN tbl t2 ON t2.slot = s.slot AND t2.day = 2
LEFT JOIN tbl t3 ON t3.slot = s.slot AND t3.day = 3
GROUP BY s.slot;
Michael
