What you are trying to do is an example of what is called "pivoting" a table or "creating a pivot table".
SELECT slot , max(if(day=1, concat(subject,' ',room), '')) as day1 , max(if(day=2, concat(subject,' ',room), '')) as day2 , max(if(day=3, concat(subject,' ',room), '')) as day3 , max(if(day=4, concat(subject,' ',room), '')) as day4 , max(if(day=5, concat(subject,' ',room), '')) as day5 from schedule group by slot The max() decides between an entry and a blank ( the entry will win if one exists) while the group by lines everything up on the same row. Friendly caution: If more than one entry exists for the same day and time, you will only see the one that is alphabetically "greater". if you wanted to see how many classes are scheduled by day for each slot (to check for conflicts) you could modify the query: SELECT slot , sum(if(day=1,1,0)) as day1 , sum(if(day=2,1,0)) as day2 , sum(if(day=3,1,0)) as day3 , sum(if(day=4,1,0)) as day4 , sum(if(day=5,1,0)) as day5 from schedule group by slot There is definitely a pattern. The columns you want as "row headers" are listed both in the SELECT **and** in the GROUP BY clauses. The values you want as columns are selectively chosen ( by the IF() functions) to return something or nothing so that one of the aggregate functions (MIN, MAX, SUM, AVG, etc) can condense those rows and columns into single values. The decisions for what gets counted in any column can be as complex as you like. If you don't like the IF() function you can use CASE statements or anything else to help you provide the correct set of values for the aggregate function you are using on that column. You can mix and match aggregate functions to be what you need. Let's say you join the appropriate tables together to form a data set that looks like : day, slot, subject, student, grade and you wanted to build a chart showing the statistics of grades vs. days and subject. You could use this type of query to work that out: SELECT day, subject , AVG(grade) as average , MIN(grade) as lowest , MAX(grade) as highest from <necessary tables> group by day, subject Now while I didn't actually need to choose values for my columns you can see that if I wanted to "pivot" that table by days (each column representing the statistics for just one day) I would have to modify they query to read: SELECT subject , AVG(IF(day=1, grade,null)) as D1_average , MIN(IF(day=1, grade, null)) as D1_lowest , MAX(IF(day=1,grade,null)) as D1_highest , AVG(IF(day=2, grade,null)) as D2_average , MIN(IF(day=2, grade, null)) as D2_lowest , MAX(IF(day=2,grade,null)) as D2_highest , .... (repeat for rest of the days) from <necessary tables> group by day, subject Notice how I had to change the IF function to return NULL values? That kept the AVG() from counting all of the other grades for the same subject but from different days. If I had used a constant, like zero, then those zeroes would have counted against us in the aggregate calculation. (The same goes for the MIN and MAX functions) I hope this helps! Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Sam Russo" <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] cc: 06/18/2004 08:34 Fax to: AM Subject: tricky timetable based query 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 Any help would be appreciated sam ********************************************************************** This message is intended for the addressee named and may contain privileged information or confidential information or both. If you are not the intended recipient please delete it and notify the sender. ********************************************************************** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]