I am building a Time off requests tool for work.

I have 3 tables: REQUESTS, COVERED, and MODID.

REQUESTS Has the following fields:

ID INT Auto_Increment not null Primary Key MODID VARCHAR(7) not null THE_DATE DATE not null THE_TIME VARCHAR(30) not null COVERED CHAR(1) not null Default = 'N'

COVERED Has the follow fields:

ID INT not null Primary Key CMODID VARCHAR(7) not null

Lastly MODID has the following fields:

MODID VARCHAR(7) not null Primary Key MODNAME VARCHAR(40) not null

When a time off request is put in REQUESTS populates with ID (auto_increment) the MODID of the mod putting in the TOR THE_DATE the mod needs covere and THE_TIME of their shift.

I have a PHP back end built that enters the above data. The same back end, when someone picks up a shift, adds the REQUESTS.ID to COVERED.ID and the MODID of the mod covering the shift. It then sets REQUESTS.COVERED = "Y" for that specific shift.

I can get a list of all the MODS that have an active time off requests using the following:

SELECT M.MODNAME, R.THE_DATE, R.THE_TIME FROM REQUESTS R INNER JOIN MODID M ON M.MODID = R.MODID WHERE COVERED = 'N';

The problem I am having is I want to get a list of all the mods whose requests is covered the date and time of their requests and the name of the mod that covered the shift EG

Requested MODNAME Date Time Covereing MODNAME

This is what I have tried:

SELECT M.MODNAME, R.THE_DATE, R.THE_TIME, C.CMODID FROM REQUESTS R, COVERED C INNER JOIN MODID M ON M.MODID = R.MODID INNER JOIN MODID M ON M.MODID = C.CMODID WHERE COVERED = 'N';

I know the above MySQL has a lot wrong with it, but I am stuck and don't know what to do.

Here is a DUMP of my MYSQL table https://dl.dropbox.com/u/12531574/TOR_2013-02-22.sql

EDIT

THE CLOSEST I CAN GET

Using:

SELECT R.MODID, R.THE_DATE, R.THE_TIME, C.CMODID FROM REQUESTS R INNER JOIN COVERED C ON C.ID = R.ID;

Outputs:

MODID THE_DATE THE_TIME CMODID AKH3 2013-02-28 10:00AM - 1:00PM PST TST3

All I want to do with that is change AKH3 (MODID) to the corresponding MODNAME in the MODID table and TST3 (CMODID) to the corresponding MODNAME in the MODID table.