CODEWITHSUNDEEP
mysqlleft-joininner-joinouter-join
Aaron Henderson
Aaron Henderson

Reputation: 191

Multiple Joins between multiple tables

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.

Upvotes: 0

Views: 1031

Answers (2)

juanschwartz
juanschwartz

Reputation: 91

Since you are only seeking out the ones with coverage, you don't NEED left joins and you should hopefully have FK Constraints in place to keep the mod lookups from breaking. This will give you what you want, with the additional second join of the MODID table giving you the covering mod's name.

SELECT M.MODNAME, R.THE_DATE, R.THE_TIME, CM.MODNAME AS `MOD COVERING NAME`
FROM REQUESTS R
JOIN MODID M ON R.MODID = M.MODID
JOIN COVERED C ON C.ID = R.ID
JOIN MODID CM ON CM.MODID = C.CMODID

I would highly suggest you change the table structure to store the CMODID to the actual integer ID of the mod as your table structure is convoluted.

Upvotes: 1

JudgeProphet
JudgeProphet

Reputation: 1729

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

Try this!

Upvotes: 0

Related Questions