Databases
StackOverflow2013
Postsdbo
POHow do I `JOIN` these tables properly?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow do I `JOIN` these tables properly?
    text
    copied!<p>I have a table being used to store aircraft inspection schedules for the current week, it is called <code>aircraft_sched</code>. There are two others that are relevant, one is called <code>aircraft_sched_options</code> which I need to <code>JOIN</code> to the first table, and the last table is called <code>aircraft</code>.</p> <p><code>aircraft_sched</code>:</p> <pre><code>column 1: AC_Reg (VARCHAR)(10),(PK),(FK -&gt; `aircraft` PK) column 2: Sched_Day1 (INT)(1),(FK -&gt; `aircraft_sched_options` PK) column 3: Sched_Day2 (INT)(1),(FK -&gt; `aircraft_sched_options` PK) column 4: Sched_Day3 (INT)(1),(FK -&gt; `aircraft_sched_options` PK) column 5: Sched_Day4 (INT)(1),(FK -&gt; `aircraft_sched_options` PK) column 6: Sched_Day5 (INT)(1),(FK -&gt; `aircraft_sched_options` PK) column 7: Sched_Day6 (INT)(1),(FK -&gt; `aircraft_sched_options` PK) column 8: Sched_Day7 (INT)(1),(FK -&gt; `aircraft_sched_options` PK) </code></pre> <p><code>aircraft_sched_options</code>:</p> <pre><code>column 1: SchedOpt_ID (INT)(1),(PK) column 2: SchedOpt_Name (VARCHAR)(10) column 3: SchedOpt_Color (VARCHAR)(7), </code></pre> <p><code>aircraft</code></p> <pre><code>column 1: AC_Reg (VARCHAR)(10),(PK) column 2: AC_SN (VARCHAR)(6) column 3: AC_Year (VARCHAR)(4) </code></pre> <p>When a new aircraft is added to the system, I have it so it also adds it to the <code>aircraft_sched</code> table. I don't think this is the right way, but that's how it is right now. So the <code>aircraft_sched</code> table is always populated with the <code>AC_Reg</code> and the <code>Sched_DayX</code> cell is either a <code>0</code> for <code>NULL</code> or a <code>SchedOpt_ID</code> number corresponding to the schedule type selected.</p> <p>The problem I'm facing is when I try to <code>JOIN</code> the <code>Sched_DayX</code> column to the <code>SchedOpt_ID</code> column. It sort of works when I only <code>JOIN</code> one column, but if I try to do more than one, then the row just disappears from my results.</p> <p>Here is my code that "sort of" works:</p> <pre><code>&lt;?php $sql = (" SELECT * FROM aircraft_sched INNER JOIN aircraft_sched_options AS aso1 ON aircraft_sched.Sched_Day1 = aso1.SchedOpt_ID "); if(!$result_sql = $mysqli-&gt;query($sql)) { echo QueryCheck("getting the aircraft schedule ","from the aircraft sched",$mysqli) . "Error No: " .$mysqli-&gt;errno; } while($ACSched = $result_sql-&gt;fetch_assoc()) { echo "&lt;tr&gt;"; echo "&lt;td class=\"ACSched_Reg\"&gt;" . $ACSched['AC_Reg'] . "&lt;/td&gt;"; echo "&lt;td align=\"center\" style=\"background:" . $ACSched['SchedOpt_Color'] . ";\"&gt;" . $ACSched['SchedOpt_Name'] . "&lt;/td&gt;"; echo "&lt;td align=\"center\" style=\"background:" . $ACSched['SchedOpt_Color'] . ";\"&gt;" . $ACSched['SchedOpt_Name'] . "&lt;/td&gt;"; echo "&lt;td align=\"center\" style=\"background:" . $ACSched['SchedOpt_Color'] . ";\"&gt;" . $ACSched['SchedOpt_Name'] . "&lt;/td&gt;"; echo "&lt;td align=\"center\" style=\"background:" . $ACSched['SchedOpt_Color'] . ";\"&gt;" . $ACSched['SchedOpt_Name'] . "&lt;/td&gt;"; echo "&lt;td align=\"center\" style=\"background:" . $ACSched['SchedOpt_Color'] . ";\"&gt;" . $ACSched['SchedOpt_Name'] . "&lt;/td&gt;"; echo "&lt;td align=\"center\" style=\"background:" . $ACSched['SchedOpt_Color'] . ";\"&gt;" . $ACSched['SchedOpt_Name'] . "&lt;/td&gt;"; echo "&lt;td align=\"center\" style=\"background:" . $ACSched['SchedOpt_Color'] . ";\"&gt;" . $ACSched['SchedOpt_Name'] . "&lt;/td&gt;"; echo "&lt;/tr&gt;"; } ?&gt; </code></pre> <p>When I say it "sort of" works, I mean that it actually displays something as a result. The reason it doesn't work is because it shows the same result in each cell even if there is only one day with a schedule type assigned. </p> <p>When I add the second <code>JOIN</code> to the query, like this:</p> <pre><code>$sql = (" SELECT * FROM aircraft_sched INNER JOIN aircraft_sched_options AS aso1 ON aircraft_sched.Sched_Day1 = aso1.SchedOpt_ID INNER JOIN aircraft_sched_options AS aso2 ON aircraft_sched.Sched_Day2 = aso2.SchedOpt_ID "); </code></pre> <p>...then it just doesn't return any rows which have a record.</p> <p>I have been reading on <code>JOIN</code>s and how they work and the only way I can think of to fix the problem is to have a separate table for each <code>AC_Reg</code> so that each day of the week can be set to a <code>UNIQUE</code> column, but I don't believe that is the best way to accomplish the task.</p> <p><strong>EDIT:</strong> </p> <p>Here are some screen shots to give a better visual.</p> <p><code>aircraft_sched</code>: </p> <p><img src="https://i.stack.imgur.com/pgAQw.jpg" alt="aircraft_sched"> </p> <p><code>aircraft_sched_options</code>: </p> <p><img src="https://i.stack.imgur.com/inObZ.jpg" alt="aircraft_sched_options"> </p> <p>My Code: </p> <p><img src="https://i.stack.imgur.com/rS7XB.jpg" alt="php code"> </p> <p>Screen Display: </p> <p><img src="https://i.stack.imgur.com/xZYPg.jpg" alt="what displays on the screen"> </p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

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