select count absences of employees on each day

alright, so lets say i have three tables, as follows :

employees(ID, name)

sessions(ID, starts_at, ends_at, day)

absences(ID, employeeID, sessionID)

lets say there are multiple sessions per day.

is it possible to select count the Absences of employees from sessions for each distinct day on the s开发者_如何转开发essions table and group them as follows :

--------------------------------
| Name |2011-01-15| 2011-01-16 | 
|      |          |            |
|john  |  2       |   0        |
|dave  |  4       |    1       |

(number of columns (days) depends on number of days on the sessions table, lets say i only want those of last or current week

thank you.

You should use the PIVOT statement. There it goes:

SELECT Name, [2011-01-15], [2011-01-16]
FROM    
(SELECT  [Day],Name
    FROM [Absences] A left join
         [Sessions] S on A.sessionID = S.ID
    GROUP BY [Day],Name) AS SourceTable
PIVOT
(
COUNT([Day])
FOR [Day] IN ([2011-01-15], [2011-01-16])
) AS PivotTable;

Look up pivot table. On some RDBMS, this is built in.

I'm thinking you are talking MySQL and you want to do a pivot table. Go to ArtfulSoftware's MySQL site and review the pivot table tutorials: http://www.artfulsoftware.com/infotree/queries.php