我如何正确地“加入”这些表格？

Question

I have a table being used to store aircraft inspection schedules for the current week, it is called aircraft_sched. There are two others that are relevant, one is called aircraft_sched_options which I need to JOIN to the first table, and the last table is called aircraft.

aircraft_sched:

column 1: AC_Reg (VARCHAR)(10),(PK),(FK -> `aircraft` PK)
column 2: Sched_Day1 (INT)(1),(FK -> `aircraft_sched_options` PK)
column 3: Sched_Day2 (INT)(1),(FK -> `aircraft_sched_options` PK)
column 4: Sched_Day3 (INT)(1),(FK -> `aircraft_sched_options` PK)
column 5: Sched_Day4 (INT)(1),(FK -> `aircraft_sched_options` PK)
column 6: Sched_Day5 (INT)(1),(FK -> `aircraft_sched_options` PK)
column 7: Sched_Day6 (INT)(1),(FK -> `aircraft_sched_options` PK)
column 8: Sched_Day7 (INT)(1),(FK -> `aircraft_sched_options` PK)

aircraft_sched_options:

column 1: SchedOpt_ID (INT)(1),(PK)
column 2: SchedOpt_Name (VARCHAR)(10)
column 3: SchedOpt_Color (VARCHAR)(7),

aircraft

column 1: AC_Reg (VARCHAR)(10),(PK)
column 2: AC_SN  (VARCHAR)(6)
column 3: AC_Year (VARCHAR)(4)

When a new aircraft is added to the system, I have it so it also adds it to the aircraft_sched table. I don't think this is the right way, but that's how it is right now. So the aircraft_sched table is always populated with the AC_Reg and the Sched_DayX cell is either a 0 for NULL or a SchedOpt_ID number corresponding to the schedule type selected.

The problem I'm facing is when I try to JOIN the Sched_DayX column to the SchedOpt_ID column. It sort of works when I only JOIN one column, but if I try to do more than one, then the row just disappears from my results.

Here is my code that "sort of" works:

<?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->query($sql))
{
    echo QueryCheck("getting the aircraft schedule ","from the aircraft sched",$mysqli) . "Error No: " .$mysqli->errno;
}

while($ACSched = $result_sql->fetch_assoc())
{
    echo "<tr>";
    echo "<td class=\"ACSched_Reg\">" . $ACSched['AC_Reg'] . "</td>";
    echo "<td align=\"center\" style=\"background:" . $ACSched['SchedOpt_Color'] . ";\">" . $ACSched['SchedOpt_Name'] . "</td>";
    echo "<td align=\"center\" style=\"background:" . $ACSched['SchedOpt_Color'] . ";\">" . $ACSched['SchedOpt_Name'] . "</td>";
    echo "<td align=\"center\" style=\"background:" . $ACSched['SchedOpt_Color'] . ";\">" . $ACSched['SchedOpt_Name'] . "</td>";
    echo "<td align=\"center\" style=\"background:" . $ACSched['SchedOpt_Color'] . ";\">" . $ACSched['SchedOpt_Name'] . "</td>";
    echo "<td align=\"center\" style=\"background:" . $ACSched['SchedOpt_Color'] . ";\">" . $ACSched['SchedOpt_Name'] . "</td>";
    echo "<td align=\"center\" style=\"background:" . $ACSched['SchedOpt_Color'] . ";\">" . $ACSched['SchedOpt_Name'] . "</td>";
    echo "<td align=\"center\" style=\"background:" . $ACSched['SchedOpt_Color'] . ";\">" . $ACSched['SchedOpt_Name'] . "</td>";
    echo "</tr>";
}
?>

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.

When I add the second JOIN to the query, like this:

$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
 ");

...then it just doesn't return any rows which have a record.

I have been reading on JOINs and how they work and the only way I can think of to fix the problem is to have a separate table for each AC_Reg so that each day of the week can be set to a UNIQUE column, but I don't believe that is the best way to accomplish the task.

EDIT:

Here are some screen shots to give a better visual.

aircraft_sched:

aircraft_sched_options:

My Code:

Screen Display:

Answer 1

If i understood your question correctly you need a join with table tbl_two for each column Day:

SELECT 
    aircraft_sched.AC_SN,
    IF(
       aso1.SchedOpt_Name IS NULL OR aso1.SchedOpt_ID = 0, 
       '-', 
       aso1.SchedOpt_Name
    ) as option1,
    IF(
       aso2.SchedOpt_Name IS NULL OR aso2.SchedOpt_ID = 0, 
       '-', 
       aso2.SchedOpt_Name
    ) as option2
FROM 
    aircraft_sched 
LEFT JOIN aircraft_sched_options AS aso1 
    ON aircraft_sched.Sched_Day1 = aso1.SchedOpt_ID
LEFT JOIN aircraft_sched_options AS aso2 
    ON aircraft_sched.Sched_Day2 = aso2.SchedOpt_ID

....

EDIT: I've updated the query and used LEFT JOIN instead of INNER JOIN to get the options because as you said some might be NULL

UPDATE : removed the join with aircraft and added check if option is null or id is 0 the - will be displayed