Heres my table structure and some sample data:
Table days
ID | name | date
1 | Thursday | 2010-06-24
2 | Friday | 2010-06-25
3 | Saturday | 2010-06-26
4 | Sunday | 2010-06-27
5 | Monday | 2010-06-28
Table positions
ID | level | name | description | parentID | active | sortOrder
1 | ICS | Incident Command | NULL | NULL | 1 | 1
210| ICS | Operations | NULL | 1 | 1 | 4
…
Table shifts
ID | type | name | startTime | endTime
1 | 1 | Day | 10:00:00 | 18:00:00
2 | 1 | Evening | 18:00:00 | 02:00:00
3 | 1 | Overnight | 02:00:00 | 10:00:00
4 | 2 | Morning | 10:00:00 | 17:00:00
5 | 2 | Afternoon | 15:00:00 | 22:00:00
6 | 2 | Night | 17:00:00 | 00:00:00
Table staff
ID | first | last | phone
1 | B | Smith | NULL
3 | J | Best | NULL
…
Table shifttopositions
ID | dayID | positionID | shiftID | staffID
…
Basically I am putting together a staffing schedular. I am trying to create a query that will return a list of shifts. when given a staffID and year parameter it would return the full list of shifts occuring for the year, and then list whether or not the member is working that shift and if so what position is he/she working. if that person is not working, it would still list the shift, but have a null value for it.
This is what I have and it works to a certain extent:
SELECT
d.`name` AS dayName,
s.`name` AS shiftName,
stf.`last`,
IF(stf.`last` IS NULL, NULL, p.`name`) AS posName
FROM shifttopositions stp
JOIN shifts s ON s.ID = stp.shiftID
LEFT OUTER JOIN days d ON stp.`day` = d.ID
LEFT OUTER JOIN staff stf ON stf.ID = stp.staffID AND stf.ID = '3'
LEFT OUTER JOIN positions p ON p.ID = stp.positionID
WHERE `date` LIKE '2010%'
GROUP BY stp.shiftID, d.ID
ORDER BY d.`date`, stp.shiftID
Now this returns the list of shifts as i want them:
dayName | shiftName | last | postName
Friday | Day | NULL | NULL
Friday | Evening | NULL | NULL
Friday | Overnight | NULL | NULL
Friday | Morning | NULL | NULL
Friday | Afternoon | NULL | NULL
Friday | Night | NULL | NULL
Saturday | Day | Best | Incident Command
Saturday | Evening | Best | Incident Command
Saturday | Overnight | Best | Incident Command
Sunday | Day | Best | Incident Command
Sunday | Evening | Best | Incident Command
Sunday | Overnight | Best | Incident Command
however, it seems to only return the last name and posName if the chosen person was the first result for that shift. Does this make sense?
For instance in the query above, i am using staffID 3. Best (staffID 3) should be listed as Incident Commander for all shifts. furthermore If I substitute staffID 1, i get all NULL results, and they should have positions in certain shifts as well.
Any suggestions would be most helpful. I can provide more info if needed. I am just at the limit of my sql ability here and need some advice. I may need to restructure the tables too. since this project is in its infancy i am open to any suggestion.
Ras
EDIT: Table Create Scripts
delimiter ;
CREATE TABLE `days` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
`date` date DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
delimiter ;
CREATE TABLE `positions` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`level` varchar(10) NOT NULL,
`name` varchar(50) DEFAULT NULL,
`description` text,
`parentId` int(11) DEFAULT '0',
`active` tinyint(1) unsigned zerofill NOT NULL DEFAULT '0',
`sortOrder` int(11) DEFAULT '0',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=217 DEFAULT CHARSET=latin1;
delimiter ;
CREATE TABLE `shifts` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`type` int(2) NOT NULL DEFAULT '0',
`name` varchar(255) DEFAULT NULL,
`startTime` time DEFAULT NULL,
`endTime` time DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
delimiter ;
CREATE TABLE `shifttopositions` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`day` int(11) NOT NULL,
`positionID` int(11) NOT NULL,
`shiftID` int(11) NOT NULL,
`staffID` int(11) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=78 DEFAULT CHARSET=latin1;
delimiter ;
CREATE TABLE `staff` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`first` varchar(255) DEFAULT NULL,
`last` varchar(255) DEFAULT NULL,
`phone` varchar(10) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=31 DEFAULT CHARSET=latin1;