Databases
StackOverflow2013
Postsdbo
POMultiple Joins between multiple tables
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMultiple Joins between multiple tables
    primarykey
    data
    text
    <p>I am building a Time off requests tool for work.</p> <p>I have 3 tables: REQUESTS, COVERED, and MODID.</p> <p>REQUESTS Has the following fields:</p> <pre><code>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' </code></pre> <p>COVERED Has the follow fields:</p> <pre><code>ID INT not null Primary Key CMODID VARCHAR(7) not null </code></pre> <p>Lastly MODID has the following fields:</p> <pre><code>MODID VARCHAR(7) not null Primary Key MODNAME VARCHAR(40) not null </code></pre> <p>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.</p> <p>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.</p> <p>I can get a list of all the MODS that have an active time off requests using the following:</p> <pre><code>SELECT M.MODNAME, R.THE_DATE, R.THE_TIME FROM REQUESTS R INNER JOIN MODID M ON M.MODID = R.MODID WHERE COVERED = 'N'; </code></pre> <p>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</p> <p>Requested MODNAME Date Time Covereing MODNAME</p> <p>This is what I have tried:</p> <pre><code>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'; </code></pre> <p>I know the above MySQL has a lot wrong with it, but I am stuck and don't know what to do.</p> <p>Here is a DUMP of my MYSQL table <a href="https://dl.dropbox.com/u/12531574/TOR_2013-02-22.sql" rel="nofollow">https://dl.dropbox.com/u/12531574/TOR_2013-02-22.sql</a></p> <p><strong>EDIT</strong></p> <p><strong>THE CLOSEST I CAN GET</strong></p> <p>Using:</p> <pre><code>SELECT R.MODID, R.THE_DATE, R.THE_TIME, C.CMODID FROM REQUESTS R INNER JOIN COVERED C ON C.ID = R.ID; </code></pre> <p>Outputs:</p> <pre><code>MODID THE_DATE THE_TIME CMODID AKH3 2013-02-28 10:00AM - 1:00PM PST TST3 </code></pre> <p>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.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAaron Henderson
    1. USAaron Henderson
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload