Harald Fuchs wrote:
"Sam Russo" <[EMAIL PROTECTED]> 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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
